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
Surendrababupakala

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 :

"ILLEGAL SYMBOL ":". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( <IDENTIFIER XMLTABLE UNNEST TABLE FINAL OLD"
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
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: 3076
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 COBOL sorting, with input GDG base COBOL Programming 7
No new posts Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts Is this possible via sort (in one pass)? SYNCSORT 4
No new posts Replacing FILLER with FILLER<SeqNu... DFSORT/ICETOOL 2
No new posts Compile Sp Cobol base COBOL Programming 1
Search our Forums:

Back to Top