Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Generic way to fetch any combinations of columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Tue Sep 18, 2012 11:21 am    Post subject: Generic way to fetch any combinations of columns
Reply with quote

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.
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Tue Sep 18, 2012 11:50 am    Post subject:
Reply with quote

I think you're trying to repeat the functions of DSNTEP2/DSNTEP4, aka, SPUFI in batch.
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Tue Sep 18, 2012 11:53 am    Post subject:
Reply with quote

Hi Gnanas - thanks for the reply. Yes, it does look the same to me as well. If only these utilities can be run without locking the tablespace?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Tue Sep 18, 2012 2:04 pm    Post subject: Reply to: Generic way to fetch any combinations of columns
Reply with quote

What is GENEXT written in?
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Tue Sep 18, 2012 2:35 pm    Post subject:
Reply with quote

Hi Bill - GENEXT is a COBOL program.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Tue Sep 18, 2012 3:57 pm    Post subject: Reply to: Generic way to fetch any combinations of columns
Reply with quote

And you are using Dynamic SQL.

Your input doesn't need to be an "actual" SQL statement. You could "annotate" it further to indicate field descriptions, then make use of that knowledge in the program and "strip off" the extra information to leave plain SQL.

Presumably you could arrange to look up the characteristics of the requested data, then you'd know.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 18, 2012 4:39 pm    Post subject:
Reply with quote

Quote:
Hi Gnanas - thanks for the reply. Yes, it does look the same to me as well. If only these utilities can be run without locking the tablespace?


1. you are not going to be able to 'beat' ibm (or 3rd party) unload utilities with program...

2. the locks taken by these utilities are based on bind parms
which your dba's can modify.

3. you are spinning your wheels trying to write a program. at best, as bill suggested, dynamic sql may/can/might/possibly reduce your overall programming effort.

4. essentially, you are trying to improve the performance of your car by using different sparkplugs.

your business units, especially marketing, drive your requirements
and they have to be satisfied.

does not matter if you have 1 table or a gazillion, you will always have this problem.

back to the sparkplug analogy:
you have to involve your dba's,
you have to get a handle on what kind of requests are made,
what tables are impacted more than others,
what can be satisfied with an image of 'yesterdays data'.

there are varied ways to handle these situations:

1. stuff that is asked for daily, weekly, monthly, periodically,
could/should be satisfied with a scheduled batch job.

2. yesterdays' data that can satisfy many requests can be downloaded to pc/servers and made available there in what ever format fits your current pc/server tools. that way, the user can fulfill his requirements thru use of excel and/or other tools.

3. there are various db2 tools to unload:
hi-performance unload
unload
dsntep2
dsntep4
and they can be configured to NOT lock everything forever.

4. utilize qmf, if you bought it. that is designed to access the db's with as little interference as possible.

good luck.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 642
Location: Whitby, ON, Canada

PostPosted: Tue Sep 18, 2012 7:53 pm    Post subject:
Reply with quote

If your main concern is about locks, then try adding "WITH UR" to your queries and then run them using DSNTEP2/4 or DSNTIAUL.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts JCL to fetch schedule status from act... parasmalik20 CA Products 1 Thu Jun 02, 2016 7:11 pm
No new posts Generic rename by pattern‏ Steve Coalbran CLIST & REXX 6 Mon May 30, 2016 9:34 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us