View previous topic :: View next topic |
Author |
Message |
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Thu Apr 28, 2005 6:35 pm Post subject: SPUFI Output |
|
|
1. Is there any way to suppress the SQL from SPUFI query?
2. Is it possible to select count as xxx and have the results horizontal instead of vertical?
Thanks
NASCAR9 |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Thu Apr 28, 2005 7:35 pm Post subject: |
|
|
Quote: |
1. Is there any way to suppress the SQL from SPUFI query?
|
Within spufi it is NOT possible. Why not unload using DSNTIAUL ? Alternatively you can use QMF which has better reporting capabilities
Quote: |
2. Is it possible to select count as xxx and have the results horizontal instead of vertical?
|
can you explain with an example?
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Fri Apr 29, 2005 9:38 am Post subject: |
|
|
Kolusu,
I need to run about 45 queries similar to the one below. I can run them all in less than 1 minute in SPUFI. My boss was running these queries in ACCESS, so I thought I could get the results quicker in SPUFI, and I can. The problem is the output, I need to do editing. Sorry for the long story. I was hoping to be able to display the results like this:
Code: |
COUNTOFMAR2004 4938
---------+---------+---------+---------+---------+---------+------
SELECT COUNT(HOURS.MEMNHR.MAR2004) AS COUNTOFMAR2004
FROM HOURS.MEMNHR
WHERE (((HOURS.MEMNHR.MAR2004)<>0));
---------+---------+---------+---------+---------+---------+------
COUNTOFMAR2004
---------+---------+---------+---------+---------+---------+------
4938
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+------
---------+---------+---------+---------+---------+---------+------
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 4
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 20
|
Thanks
NASCAR9 |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Fri Apr 29, 2005 9:55 am Post subject: |
|
|
Nascar9,
A batch unload will give you the desired results.
Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(xxx)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('DB2P.RUNLIB.LOAD')
//SYSREC00 DD DSN=YOUR OUTPUT OF ALL 45 QUERIES,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(TRK,(1,1),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT CHAR('COUNT OF MAR 2004 IS: ')
,DIGITS(COUNT(HOURS.MEMNHR.MAR2004))
FROM HOURS.MEMNHR
WHERE HOURS.MEMNHR.MAR2004 <> 0
UNION
SELECT CHAR('COUNT OF APR 2004 IS: ')
,DIGITS(COUNT(HOURS.MEMNHR.APR2004))
FROM HOURS.MEMNHR
WHERE HOURS.MEMNHR.APR2004 <> 0
UNION
...
code all your othe queries here with UNION
;
/*
|
The output from this job will be as follows
Code: |
COUNT OF MAR 2004 IS: 0000000143
COUNT OF APR 2004 IS: 0000000035
|
Hope this helps...
Cheers
kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Fri Apr 29, 2005 12:13 pm Post subject: |
|
|
Kolusu,
This is GREAT! As I have stated before we are a fairly new MVS/DB2 shop. I have found So much information on both on this site.
Thank You!
NASCAR9 _________________ Thanks,
NASCAR9 |
|
Back to top |
|
|
|
|