DB2 SQL referencing a list

[a2love]

Hi,

I'm writing an SQL pull from a DB2 database as follows (not completely debuged but u get the picture):

SELECT POLICY
,DEC (SUM (TBASICPRM), 12,2)
FROM CMIDBO.PREMTRAN
WHERE COMPANY = 1
AND PRODUCT IN ('COM' , 'FRM')
AND POLICY IN (119102207
,628332100
,855064102
)
GROUP BY POLICY
WITH UR
;

What I would like to do is for the 'AND POLICY IN ()" part reference a list of policies from a file location (instead of having to manually change on every run).

The data is currently siting in a sequential file created on an earlier stage.

Any suggestions?

Thanks,
Adam

[DSguru2B]

Parameterize it. Run this job via a sequence job so that you can pass

Code: Select all

Field(cat list.txt, @FM, 1)

to get the list and pass it to the parameter.

[ray.wurlod]

Use a job parameter to contain the entire IN list. Your query then looks like:

Code: Select all

SELECT POLICY ,DEC (SUM (TBASICPRM), 12,2) 
FROM CMIDBO.PREMTRAN 
WHERE COMPANY = 1 
AND PRODUCT IN ('COM' , 'FRM') 
AND POLICY IN (#PoliciesList#) 
GROUP BY POLICY 
WITH UR 
; 

Populate the IN list (by whatever method you desire, perhaps a routine that reads the file) in a job sequence that invokes this job through a Job activity. Some error handling has been omitted for clarity:

Code: Select all

FUNCTION PolicyList(PoliciesFile)
* Argument is pathname of file containing policies to process.
Ans = ""
OpenSeq PoliciesFile To fPoliciesFile
Then
   Loop
   While ReadSeq Line From fPoliciesFile
      Ans<-1> = Line    ; * assumes one policy per line
   Repeat
   Convert @FM To "," In Ans
   CloseSeq fPoliciesFile
End
Else
   Call DSLogWarn("Unable to open file.", "PolicyList")
End
RETURN(Ans)

[a2love]

so i guess this is where the premium content comes in lol.

[DSguru2B]

And well worth it. The entire routine is right there.

[a2love]

You guys are salesmen and scholars I see . I have informed by boss of the high quality of support your site provides. We will see if he clears the user purchase. Who knows, may lead to multiple users

Any chance I can see the code in the mean time? lol

[a2love]

Re-opened this post because I have a question regarding the FUNCTION PolicyList(PoliciesFile) script you recommended Ray.

I understand #PoliciesList# will reference the function, however where do you suggest I actually write the FUNCTION section? Within the SQL area of a DB2 stage after the SELECT statement?
I'm abit new to SQL and am unsure of its use within Datastage.

Thanks in advance,

Adam

[ray.wurlod]

A Routine activity in a job sequence

[a2love]

hey Ray. Thanks for the reponse. Do you know where I can locate documentation on where to write the routine in datastage? my IBM manual is lacking... at best.

[ray.wurlod]

Not really. There is a class that the vendor used to offer, called Programming with DataStage BASIC, and one with a similar title that I offer (see website), but not much in the way of documentation per se.

[a2love]

Ok I used all my "resources" and was able to create the routine to pull the data. It worked :D .

In my job sequence I have a simple routine activity linked to a parellel job (containing the DB2 sql pull). On runtime I recieve the following error:

Code: Select all

db2testing..JobControl (@Copy_of_jobDB2FNDtest): Controller problem: Error calling DSSetParam(ppFNDPolicyList), code=-4
[ParamValue/Limitvalue is not appropriate]

I set up my job parameter ppFNDPolicyList in parellel job DB2FNDtest as a string, so I assumed the string of values I returned with the subroutine would work well... however it seems it does not like this Paramvalue.
Any suggestions?

p.s. I attempted a different approach using Exec. command stage and was getting some help on a different post today... I was getting the same error.

Thanks for your help again.

[vmcburney]

There are two guides to programming in DataStage basic. There is a section in the Server Job Developers Guide and there is a DataStage BASIC guide. There are also some example SDK routines. There is also a large archive of code in this forum if you just search for specific commands such as DSSetParam or ReadSeq.

[tiozzo]

Thanks for the sources.


I have created a working routine that a Routine stage brings in the following info:

''0123450000','0123450008','0412354009','0123456008''

This is brought in as a string. It is important I keep the single quotations around the numbers as I am using this string as a parameter value in the sql IN command found in the SQL code at the top of this post.

When assigning this string to a parameter (defined as a string) I recieve the following error:

Code: Select all

db2testing..JobControl (@Copy_of_jobDB2FNDtest): Controller problem: Error calling DSSetParam(ppFNDPolicyList), code=-4
[ParamValue/Limitvalue is not appropriate]

Can this string even be set to a parameter as a string with these single qoutations?

[ray.wurlod]

Is this a String parameter type, or some other type? You should not get this message for String parameters. You seem to be missing the leading and trailing single quote characters, but what's between the double quotes is still a valid string - this would cause problems in your IN clause later.

Can you please post your routine code? Encase it in Code tags.

[a2love]

Hi Ray,

I have defined the Parameter type as String.
Here is my routine:

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 code is being used on a sequential file that appears like this:

Code: Select all

'0136530000'
'0322430008'
'0414643009'
'0532516008'

However I can save the data to the sequential file in this format, if it is easier:

Code: Select all

'0136530000','0322430008','0414643009','0532516008',

So, with the first type of sequential file I am assuming I am required to use a Field() command? or should it accept as is?