| View previous topic :: View next topic |
| Author |
Message |
js01
Beginner
Joined: 13 Oct 2005
Posts: 84
Topics: 32
Location: INDIA
|
 Posted: Tue Aug 15, 2006 1:33 pm Post subject: How to get selected records giving file as input |
 |
|
All,
I have a file which is having some key data
using this file as an input I should get selected records depending on key data which in input file.
Can you please provide me sample query.
I know that we can achieve this using Cobol program. but want to run a query.
Tank you |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
js01
Beginner
Joined: 13 Oct 2005
Posts: 84
Topics: 32
Location: INDIA
|
| Posted: Tue Aug 15, 2006 6:45 pm Post subject: |
|
|
Kolusu,
i have gone thru the link
can you please provide me the JCL (full)
thank you |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Aug 15, 2006 7:11 pm Post subject: |
|
|
js01,
Here is the sample unload JCL. You need to pass the sysin parms as shown in the other post.
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('XXXX.RUNLIB.LOAD')
//SYSREC00 DD DSN=YOUR UNLOAD FILE,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=CYL,(X,Y),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
your select statement here;
/*
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
js01
Beginner
Joined: 13 Oct 2005
Posts: 84
Topics: 32
Location: INDIA
|
| Posted: Tue Aug 15, 2006 7:14 pm Post subject: |
|
|
kolusu,
I used this jcl but i was getting
| Code: |
DSNT502I ERROR IN DSNTIAUL SQL INPUT:
ELECT PROD_REF_I F
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FROM, TOKEN IS <HEXSTRI
0 DSNT502I ERROR IN DSNTIAUL SQL INPUT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE
<GRAPHSTRING>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN |
JCL
| Code: |
//SYSTSIN DD *
DSN SYSTEM (DB2T)
RUN PROGRAM (DSNTIAUL) PLAN(DBATIAUL) -
PARM('SQL')
END
/*
//SYSIN DD *
SELECT PROD_REF_I FROM OWNER.PRODUCT_TABLE
/*
|
CAN YOU PLEASE ADVISE |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Aug 15, 2006 8:58 pm Post subject: |
|
|
| Quote: |
RUN PROGRAM (DSNTIAUL) PLAN(DBATIAUL) -
PARM('SQL')
|
js01,
That Plan is DSNTIAUL you coded it as DBATIAUL and you are missing a Loadlib statement also
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Wed Aug 16, 2006 6:34 am Post subject: |
|
|
Also you should NOT use the first character of the SYSIN JCL data for meaningful input. That means: shift your SQL statement one character to the right. As seen in your error message, the module DSNTIAUL receives the keyword "ELECT ..." from your SYSIN stream and has problems to interpret this information.
regards
Christian |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Aug 16, 2006 7:38 am Post subject: |
|
|
| Quote: |
Also you should NOT use the first character of the SYSIN JCL data for meaningful input. That means: shift your SQL statement one character to the right.
|
CZerfas,
Utilities running with IKJEFT01 CAN have the sysin cards start from pos 1. Utilities like Sort, IDCAMS have the restriction of control cards starting from pos2. DSNTIAUL runs fine even when you are control cards are starting from pos1.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
js01
Beginner
Joined: 13 Oct 2005
Posts: 84
Topics: 32
Location: INDIA
|
| Posted: Wed Aug 16, 2006 1:55 pm Post subject: |
|
|
Kolusu,
i am sorry ..its typo.
Actually i used same thing that you mentioned ,can you please help me.
JCL is:
| Code: |
//JOBLIB DD DISP=SHR,DSN=TDB2.LOADLIB
// DD DISP=SHR,DSN=TDB2T.DSNLOAD
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2T)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('TDB2T.RUNLIB.LOAD')
//SYSREC00 DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT SNAME FROM S
/*
Error msg:
DSNT502I ERROR IN DSNTIAUL SQL INPUT:
SELECT SNAME FROM S
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT502I ERROR IN DSNTIAUL SQL INPUT:
SELECT SNAME FROM S
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT502I ERROR IN DSNTIAUL SQL INPUT:
ELECT SNAME F
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FROM, TOKEN IS <HEXSTRIN
EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 14 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
DSNT502I ERROR IN DSNTIAUL SQL INPUT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME SYMBO
<HEXSTRING> <CHARSTRING> <GRAPHSTRING>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 4 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Aug 16, 2006 1:57 pm Post subject: |
|
|
js01,
You need a SEMI-COLON (;) at the end of your SQL statement. Check and see the example I posted
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
js01
Beginner
Joined: 13 Oct 2005
Posts: 84
Topics: 32
Location: INDIA
|
| Posted: Wed Aug 16, 2006 1:59 pm Post subject: |
|
|
Thank you very much kolusu,
it is working fine... |
|
| Back to top |
|
|
js01
Beginner
Joined: 13 Oct 2005
Posts: 84
Topics: 32
Location: INDIA
|
| Posted: Thu Aug 17, 2006 2:37 pm Post subject: |
|
|
Hi,
i have another issue here that i am not getting the cloumn that is mentioned in where cluase as below
| Code: |
SELECT S#,SNAME,STATUS,CITY FROM S
WHERE STATUS IN (00,
20,
10,
30,
20,
30);
|
reslut is:
getting only three columns
any one please suggest me.
Note: when i run same query thru spufi getting mentioned column in resluts |
|
| Back to top |
|
|
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Thu Aug 17, 2006 2:56 pm Post subject: |
|
|
You've used SYSREC00 DD SYSOUT=*, instead of SYSOUT=*, try coding a dataset there and you would get the intended results.
________
medical cannabis
Last edited by coolman on Sat Feb 05, 2011 1:48 am; edited 1 time in total |
|
| Back to top |
|
|
js01
Beginner
Joined: 13 Oct 2005
Posts: 84
Topics: 32
Location: INDIA
|
| Posted: Thu Aug 17, 2006 3:17 pm Post subject: |
|
|
I tried with dataset set also...didn't get desired resluts...i feel that there is no diffrence...
please help me |
|
| Back to top |
|
|
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Fri Aug 18, 2006 1:59 am Post subject: |
|
|
js01,
Why are u using STATUS values to be checked 2 times for values 20 and 30 and you need not need to include 2 times.Try the code where you will get all the displays of the column values.Hope this helps.
| Code: |
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT S#,SNAME,CHAR(STATUS),CITY FROM S
WHERE STATUS IN (00,
10,
20,
30);
/*
|
OUTPUT
| Code: |
---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8-
********************************* TOP OF DATA **********************************
01 SHEKAR 0 BANGALORE
02 KOLUSU 10 BANGALORE
03 VIVEK 20 BANGALORE
04 JS01 30 BANGALORE
05 FRANK 0 BANGALORE
|
You could also use a dataset as output instead of SYSOUT=*
Try this code:
| Code: |
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=SHEKAR.DB2.UNLOAD,DISP=(NEW,CATLG,CATLG),
// SPACE=(TRK,(1,1),RLSE),UNIT=SYSDA
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT S#,SNAME,CHAR(STATUS),CITY FROM S
WHERE STATUS IN (00,
10,
20,
30);
/*
|
OUTPUT DATASET
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
********************************* Top of Data **********************************
01.SHEKAR .0 .BANGALORE .
02.KOLUSU .10 .BANGALORE .
03.VIVEK .20 .BANGALORE .
04.JS01 .30 .BANGALORE .
05.FRANK .0 .BANGALORE .
|
Check this link for CHAR Scalar Function:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/3.2.11?SHELF=&DT=20010718164132&CASE=
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
