MVSFORUMS.com

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

Hi,
My problem may seem quite simple to proficients,but I'm new to DB2, so pls help me out.It goes like this -
I've 21 tables and each table has records with a field named SYS_ID in it. This SYS_ID field can have 84 different values.
Now , I want to know how many records are there corresponding to each SYS_ID and I want to store each count in a different variable.[So the SELECT COUNT(*) GROUP BY won't work here].e.g. For SYS_ID='xxx', I want to store the count of no. of records in a variable named XXX-COUNT and likewise.
I don't want to write 84 SQL queries for this Rolling Eyes

How to achieve this using minimal SQL statements?
Thanks in advance.
_________________
Regards,
Mangsk

kolusu · Posted: Mon Jun 28, 2004 5:07 am Post subject:

Mangsk,

Please post your question with an appropriate title. A Title like "Db2 Problem" mean nothing. A More approriate title would be "Sql for count of SYS ID's". I am editing the topic title.

You don't need 84 queries, you just need 21 queries for 21 tables.

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

kolusu,
I'm afraid that won't really satisfy my requirement.
I want to know how many records are there corresponding to each SYS_ID and I want to store each count in a different variable.[So the SELECT COUNT(*) GROUP BY won't work here].e.g. For SYS_ID='xxx', I want to store the count of no. of records in a variable named XXX-COUNT and likewise.

Hope this clears Razz

.
_________________
Regards,
Mangsk

kolusu · Posted: Mon Jun 28, 2004 5:26 am Post subject:

mangsk,

Are you doing this in a program ? or spufi? if you are doing this in a program, then open the cursor with the above query , fetch each record and store it in the approriate variable checking the value of sys-id

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

Well, I'm doing this in the program. Defining the cursor wil have a problem like this.
Say,in the file there is no record corresponding to SYS-ID, then I want the count variable XXX-COUNT populated as 0 (zero).But in case of SELECT COUNT (*) or cursor,this won't be possible.What say? Crying or Very sad

TIA.
_________________
Regards,
Mangsk

kolusu · Posted: Mon Jun 28, 2004 7:53 am Post subject:

Mangsk,

*Sigh* This is one of the simplest exercise and you seem to imagine it as huge task.

Just define the 84 variables in your program.

mangsk · Beginner Joined: 05 Jun 2004 Posts: 46 Topics: 18

Thanks Kolusu for the reply..
Then I need to define 21 cursors, 1 for each table Sad

Actually, I wanted to find out if there is any other simpler way to do it.. Idea

_________________
Regards,
Mangsk

kolusu · Posted: Tue Jun 29, 2004 11:32 am Post subject:

Ravi,

If you have more than 1 select statement as input for the sample unload program DSNTIAUL, then you also need different SYSRECnn datasets also. Every Sql Statement is unloaded to a different SYSREC dataset.

The result of the first sql statement is stored in the dataset attached to the DD name SYSREC00.

The result of the second sql statement is stored in the dataset attached to the DD name SYSREC01.

And so on.

Actually he can use just one cursor using an Union clause

kolusu · Posted: Tue Jun 29, 2004 12:11 pm Post subject:

Ravi,

You are showing me the output listing of DSNTEP2 which will write out the results of your sql query to sysprint. Your First post you have DSNTIAUL utility which requires SYSRECnn datasets.

DSNTEP2 and DSNTIAUL are 2 different utilitites. The following is the JCl required to run DSNTEP2.