IBM Mainframe Forum Index
Log In
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register

Dynamically pass table name to a select query in COBOL DB2

IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message

New User

Joined: 18 Aug 2023
Posts: 1
Location: INDIA

PostPosted: Mon Aug 21, 2023 4:00 pm
Reply with quote

Hi! This is my First post in our Forums!

I have a requirement to select count of each table (over 200 tables) and update the count values in another table (on daily basis). can you any let me know if we have a way to achieve this in COBOL DB2 program?

I tried to pass the table name from input file and use the table name in the COBOL program like below

select count(*) from :WS-TABLE-NAME
with ur;

getting below error :

Back to top
View user's profile Send private message

Senior Member

Joined: 29 Apr 2008
Posts: 2050
Location: USA

PostPosted: Mon Aug 21, 2023 5:11 pm
Reply with quote

COBOL is a bad choice in this particular case.

In COBOL you may need so called "dynamic SQL"; it is a serious headache, especially for your so trivial task.

Sample COBOL dynamic SQL program

In your case the goal can be achieved in a 100 times more simple way:

1) get the list of required table names as text strings, using any primitive tool (SPUFI, SELECT in SYNCSORT, 100 other methods),

2) combine the list of table names into a series of full SQL queries: "SELECT COUNT(*) FROM {table_name};"
Any convenient text-processing tool can be used; I personally did this successfully from SYNCSORT, just after getting the list of tables.

3) Run the series of created SQL queries using any primitive tool, like SPUFI again, or whatever you prefer.

Also REXX SQL can be used, if you are familiar with it. REXX is using dynamic SQL for any query.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 3059
Location: NYC,USA

PostPosted: Tue Aug 22, 2023 2:48 pm
Reply with quote

Welcome !

Or another easiest way is to get it from db2 catalog table systable ( CARD ) column .

The beauty is if you have these tables in certain database then just one query with database name in the where clause and that will give you count of each table in that db.

And if to do per table then try this approach-
Step1 . Run a RUNSTAT utility to update counts in systables
Step2 . prepare a sql query where with all your table names will be part of a IN clause ( use DFSORT or SYNCSORT to create a sql query using build operator and provide a data set with all your table names in it as a input )
Step3. run thru batch spufi to get desired counts using step2 output.
Step4. Using db2 load replace utilities load the dataset from step3.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2


Similar Topics
Topic Forum Replies
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
No new posts COBOL 6.4 - User Defined Function nee... COBOL Programming 6
No new posts Inserting into table while open selec... DB2 1
No new posts Replace each space in cobol string wi... COBOL Programming 3
Search our Forums:

Back to Top