Dynamic build of Job Parameter for defining SQL source

[zbethem]

Here's the design issue:

I have a set of reference tables. In these reference tables there will be a where clause for a given rule. I want to take this where clause, set it to a job parameter, then use it to define a dynamic SQL. There are some key considerations:

I have to process multiple rules
For each rule, a separate where clause exists

I was thinking the following, but I'm getting hung up on the input definition for OracleOCI.

Code: Select all

ODBCsource--->transformer------>OracleOCI------>transformer--->seqfile
(rules)     (set job param)   (dynamic SQL)      (downstream processing)

Should I be dividing this up some other way? Has anyone out there ever have to tackle a similar problem?

Thanks in advance.

[kcbland]

Won't work. You're saying that the WHERE conditions vary for each row being processed? Meaning each row constructs a WHERE clause, which may include 1, 2, 3, or more columns? The query is declared as a cursor, so there's fixed input variables. You can't suddenly use 2 columns for a WHERE condition on one row and then switch to 3 columns on the next row.

Let me think for about 5 minutes and I'll come back with some ideas.

[kcbland]

Consider:

1. A stored procedure that takes a fixed number of inputs for the WHERE clause, with a code value that tells the SP how to construct the proper dynamic SQL and then issues the appropriate SQL.

2. Break your existing job into 2 parts. Job one is ODBC-->XFM-->SEQ where the SEQ file contains a fully crafted SQL query for each row with a UNION ALL between rows that will return a uniform set of columns. Job two is OCI-->XFM-->SEQ where the OCI stage executes the generated script (you can do this, it's cool) and streams the output to the transformer, to finish the transformation of the data.

[ogmios]

For the OP's problem... a way out via DataStage would be by writing a pure BASIC job. In BASIC you can compose your SQL based on some reference table and then execute that.

It's harder than just a GUI job, the problem with those is that you can't dynamically create SQL statements. There are some ways around it like injecting your SQL snippets via parameters (but you don't want to startup a job per input row).

And even when doing it in pure BASIC, you may have other problems... if e.g. the output colums can differ between rules.

Another solution would be to use a stored procedure in which you do your stuff.

Qua difficulty BASIC and stored procedure are about as hard. If you have good databases developers use a stored procedure, if you have good DataStage developers use the BASIC.

Ogmios

[zbethem]

2. Break your existing job into 2 parts. Job one is ODBC-->XFM-->SEQ where the SEQ file contains a fully crafted SQL query for each row with a UNION ALL between rows that will return a uniform set of columns. Job two is OCI-->XFM-->SEQ where the OCI stage executes the generated script (you can do this, it's cool) and streams the output to the transformer, to finish the transformation of the data.

Well, look like I'll try to keep up with the cool kids. :D
The two job approach with SQL in the Sequential file seems like a good deal. Thanks for the tips everyone.