I am trying to read from a few DB2 tables by running different (stand-alone) SQLs and send it to a file. In the DB2_UDB_API stage, I am using 'Custom SQL statement' and it appears that only one SQL can be entered here. Please let me know if there is a different approach to achieve this. Thanks in-advance.
Thanks. UNION does not work even in TOAD, as the return values from the SQLs are different numbers (One SQL returns 2 fields, another 5 fields so on). Multiple source stages is an option but since there are many SQLs, I am keeping that as a last option. Not sure if I go for 'Load SQL from File at Run time' will work in this scenario.
Of course a union would require the same number (and type) of output fields between all of the sql statements, that's how it works. Can you pad them out so they all are similar, selecting NULL or "" or 0 for the 'missing' columns?
And no, loading the sql from a file won't make any difference.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Thanks Craig. When I tried 2 SQLs with union (same no and type), following error appears. 'DB2_UDB_API_29: The 2 column(s) defined on this link do not match the 2 column result set generated by the SQL statement:.....".
Then it would seem your column metadata in the stage don't match what the SQL is delivering. I've done this without issue in Oracle but I have no clue if DB2 adds any 'special restrictions' to this process.
-craig
"You can never have too many knives" -- Logan Nine Fingers
OK, when I tested with 2 SQLs, I defined 2 O/P Columns, but should have been one column only as my SELECT query returns only one column but 2 rows. So, that is resolved now. But still, for mixed no of result sets (multiple SQLs), I need to figure out a design.
For that you will need multiple source stages and some way to homogenize the data before you funnel it all together... unless your file struture will support multiple record types.
-craig
"You can never have too many knives" -- Logan Nine Fingers
