DSXchange

Hi,

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.

Either do a UNION there or use multiple source stages and a Funnel.

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.

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.

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.

Yes, that is what I am doing. Happy New Year. Thanks.