Passing Routine Output as Parameter into SQL Query

[a2love]

Hi all,

Had help with parts of this recently but can't get to the end result.

I have a Routine which reads a list, passes the list to a parameter in string format, and a Job which trys to use this parameter in a WHERE clause in a DB2 SQL Query. Here are the specifics:

The Routine is as follows:

Code: Select all

Ans = ""
OpenSeq FoundationPoliciesFile to fFoundationFile
Then
    Loop
    While ReadSeq Line from fFoundationFile
        Ans<-1> = Line;
    Repeat
    Convert @FM To ',' In Ans
    CloseSeq fFoundationFile
End
Else
    Call DSLogWarn("Unable to open file.", "FoundationPoliciesFile")
End
RETURN(Ans)

This produces the following conditional error in the Log, however seems to still pass the value:

Code: Select all

db2testing..JobControl (@Routine_Activity_12): Routine DSU.srCPCFoundationPolicies did not finish OK, return code = ''0136530000','0322430008','0414643009','0532516008''

I assume this is an issue, however I setup 2 triggers: an if Fail, and an otherwise both pointing to the same job.

The SQL code ran in the DB2 stage is as follows:

Code: Select all

SELECT CTR_POL_NUM, CTR_RSA_TOT_PREM
FROM  DMIDBO.CTR_TRANSACTION
WHERE 
CTR_POL_NUM IN ( #FNDPolicyList# ) and
CTR_SOURCE_SYSTEM IN ( 'HC' , 'HF' )
GROUP BY CTR_POL_NUM
WITH UR;

This code then produces some fun errors, which are not making sense to me:

Code: Select all

APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver][DB2] SQL0199N  The use of the reserved word "IN" following "" is not valid.  Expected tokens may include:  "FOR WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZE ".  SQLSTATE=42601
SQLExecDirect: Error executing statement 'SELECT CTR_POL_NUM, CTR_RSA_TOT_PREM
FROM  
WHERE CTR_POL_NUM IN (CTR_SOURCE_SYSTEM IN ('HC' , 'HF')
GROUP BY CTR_POL_NUM
WITH UR
'.  See following DB2 message for details.

There have been some variations on the error, but they all revolve around changing the values of my parameter that im passing, removing it completely (and sometimes taking parts of the sql code with it).

The parameter is a String and must contain the single quotations for the SQL WHERE clause.

Anyone have any suggestions on how to proceed/workaround? This is my last bottleneck of a project im working... any help would be greatly appreciated.

Thanks,
Adam Love

[ray.wurlod]

You seem to have two IN operators in one clause:

Code: Select all

WHERE CTR_POL_NUM IN (CTR_SOURCE_SYSTEM IN ('HC' , 'HF') 

The "did not finish OK" message is a strange artifact of Routine activity; it uses the same convention as Execute Command, regarding the return value as an "exit status"; if it's non-zero it's regarded as problematic. You could create a Custom Trigger such as Routine.$ReturnValue >= "", but I believe the warning is still generated. Use a message handler to demote it to informational.

[DSguru2B]

As a different approach. Put all your values in a line in the format it should appear in the sql clause. In the execute command stage read the contents of the file

Code: Select all

type my/fully/qualified/file.txt

Pass the Execute_Command.$Output<1> or Field(Execute_Command.$Output,@FM, 1) as the derivation for the parameter value.

[tiozzo]

Hi,

Thanks for the responses. Ray I will try that soon. DSGuru I have implemented the Execute command as follows:

Command executed (unconditionally):

Code: Select all

type \\Nho04\micpriv\Adam\CPC_Datasets\ER_CPC_Unmatched_HUON_PolicyNumbers.txt

Value brought into job as a parameter as follows:

Code: Select all

Field(Execute_Command_8.$CommandOutput,@FM,1)

Seems to import fine (I have no errors).
However I recieve the same error within the job as with Routine stage:

Code: Select all

APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver][DB2] SQL0199N  The use of the reserved word "IN" following "" is not valid.  Expected tokens may include:  ") , ".  SQLSTATE=42601
SQLExecDirect: Error executing statement 'SELECT CTR_POL_NUM, CTR_RSA_TOT_PREM
FROM  DMIDBO.CTR_TRANSACTION
WHERE 
CTR_POL_NUM IN ( CTR_SOURCE_SYSTEM IN ( 'HC' , 'HF' )
GROUP BY CTR_POL_NUM
WITH UR'.  See following DB2 message for details.

See my first post for what my SQL code actually is. The message is strange as it is skipping some info of the sql code. Any ideas? Should I change the triggers and create some different ones other than unconditional as Ray reccomended if I was using the Routine stage?

Thanks,
Adam

[kcbland]

My opinion is your design is flawed. You should investigate if you can create a work table you can clear and populate prior to running your job. Put your list of values into that table and then use a join (or change your query from an IN list to an IN (select...) statement.

For a large list of values, you may even exceed the query parser limits for characters in a SQL statement.

If you persist with your design, have you experimented with just getting the parameter-ized query to work without using the job control? I'm confused because I can't separate where your problem is occurring. If you get the job working perfectly (use a default parameter value to test), then the only issue is feeding in the parameter value.

The job parameter passing APIs like to strip out quotes using dsjob, so maybe you're going to experience something similiar as well on your first and last values. You could also be up against a maximum parameter length allowed.

You may consider building the entire SQL statement dynamically and writing it to a file, then setting up your OCI stage to run the SQL in a file, versus your current method. I don't know your volumes, but you sometimes can do fancier things using the OCI instead of Enterprise stage.