Generic way to fetch any combinations of columns

Go down

Generic way to fetch any combinations of columns

Post  kasthuri on Mon Jan 28, 2013 12:07 pm

Hi - we keep getting requirements from the business to provide them random extracts from tables. We have around 1230 tables / 20k columns in all. Based on the business requirements, we have to write a program to extract the data from the tables using SELECTs.

We could use a Db2 utility to unload the data but the locks that UNLOAD utilities puts on the table space make them unavailable for the time of unload. Hence, we go with writing a program each time.

I am thinking of an idea to make this extraction process very generic so that whenever business comes up with such a request, we break that down into a simple SELECT query. Then we pass this SELECT query into the generic program and fetch all the data.

I have written a small prototype like below for clarity.
Code:

//X01 EXEC PGM=IKJEFT01
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSABOUT DD SYSOUT=*
//SYSDBOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBA0)
RUN PROGRAM(GENEXT) PLAN(PLAND)
END
/*
//INSQL DD *
SELECT CUST_NUM, NAME
FROM MYTS.CUSTOM_TABLE
WHERE GENDER = 'M'
/*


I am declaring a cursor in the program using this SQL and then doing a FETCH. Now here is where i am stuck...

I can DECLARE the cursor using SELECT statement that i get from instream data. But how can i make sure that i read correctly in to the correct host variables (where i can have INT, SMALL, DATE, TS, CHAR, VARCHAR) which could be any random combinations.

Could you please share your thoughts on this. I might be thinking of doing something which is not technically possible, if so, could you share your ideas on any other way to achieve this.

Do let me know if any details are required, i might not have been able to explain clearly.

Thanks in advance.

kasthuri

Posts : 212
Join date : 2013-01-27

View user profile

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum