Dynamically pass table name to a select query in COBOL DB2

Surendrababupakala · New User Joined: 18 Aug 2023 Posts: 1 Location: INDIA

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 :

"ILLEGAL SYMBOL ":". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( <IDENTIFIER XMLTABLE UNNEST TABLE FINAL OLD"

sergeyken · Posted: Mon Aug 21, 2023 5:11 pm

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.

Rohit Umarjikar · Posted: Tue Aug 22, 2023 2:48 pm

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.