View previous topic :: View next topic
|
Author |
Message |
Taranprietsingh
New User
Joined: 25 Nov 2021 Posts: 10 Location: UK
|
|
|
|
Hi Team,
I am executing an SQL query using PGM=IKJEFT01 and PROGRAM(DSNTIAUL). Also, using scalar function CHAR, for extracting the data via an SQL query from single or multiple tables. For this, I have kept the syspunch as dummy. The output file (SYSREC) is generated as a FB file. My first query is can we create a VB file in such a situation? My second issue is that I am getting trailing spaces at the end of each record of the FB output file.
if the same file is a VB file, when we view it in browse mode with HEX ON, it has no spaces but when FB file is viewed in the browse mode with HEX ON, it shows the spaces present.
Below is the job that I am executing.
//UNLOAD EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2P)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
LIB('DB2P.LOADLIB') PARM('SQL')
END
/*
//SYSPUNCH DD DUMMY
//SYSREC00 DD DSN=DEV@@ID.SFOCUS.CONV.EPSOMTAF.SUP460,
// DISP=(NEW,CATLG,DELETE),UNIT=(SYSDP,3),
// SPACE=(CYL,(450,550),RLSE)
//SYSIN DD *
SELECT CHAR(COUNT(*) ||',"' ||
SM_MERCH_CAT ||'","' ||
SM_MERCH_CODE ||'",' ||
TO_CHAR(SM_TAF_START_DATE,'YYYYMMDD') ||',' ||
TO_CHAR(SM_TAF_END_DATE,'YYYYMMDD') ||',' ||
SM_TRADING_ADJ_FT ||',"' ||
UPDATE_LOGON_ID ||'","' ||
CHAIN_LETTER ||'"')
FROM OPCSUB.SMVTAF
WHERE NOT STORE_CODE = '4910'
AND NOT CHAIN_LETTER = 'V'
GROUP BY SM_MERCH_CAT, SM_MERCH_CODE, SM_TAF_START_DATE,
SM_TAF_END_DATE, SM_TRADING_ADJ_FT, UPDATE_LOGON_ID,
CHAIN_LETTER
FOR FETCH ONLY;
/*
Output file with HEX ON:
------------------------------------------------------------------------------
1,"IL","CITAIDS.BENEFIT ",20211117,20211130,2.00,"CITAIDS","B"
F67CD767CCECCCE4CCDCCCE476FFFFFFFF6FFFFFFFF6F4FF67CCECCCE767C7444444444444444444
1BF93FBF3931942B25556930FB20211117B20211130B2B00BF3931942FBF2F000000000000000000
------------------------------------------------------------------------------
1,"IL","CITAIDS.ELOFFER ",20211201,20211207,1.10,"CITAIDS","B"
F67CD767CCECCCE4CDDCCCD476FFFFFFFF6FFFFFFFF6F4FF67CCECCCE767C7444444444444444444
1BF93FBF3931942B53666590FB20211201B20211207B1B10BF3931942FBF2F000000000000000000
------------------------------------------------------------------------------ |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2226 Location: USA
|
|
|
|
1) keeping SYSPUNCH as DUMMY, or not - has no effect on your issue.
2) I am on vacation now, so right now I cannot give you a working example: how I successfully created an unloaded dataset with RECFM=VB. Can do it after 12/07/2021
3) Please, use the CODE TAGS when presenting any part of your code
4) the HPU unload utility (if available) can do all your conversions automatically, by default.
5) one different approach would be: to unload the DB2 table without any conversion, and in the next step to process the simple unloaded data using a SORT utility. |
|
Back to top |
|
 |
Taranprietsingh
New User
Joined: 25 Nov 2021 Posts: 10 Location: UK
|
|
|
|
Thank you so much for your reply.
All the columns that are being fetched are ok, just the trailing spaces at the end of each record is an issue. I have tried STRIP, TRIM, REPLACE, etc functions but no success. May be I am not using those functions properly.
Also, I read online that we cannot create a VB file using DSNTIUAL utility. Is that true? I believe those trailing spaces are due to CHAR function or the data conversion thats happening when fetching the records.
What I need is to do something within the SQL query so that trailing spaces do not appear. Please help. Many thanks in advance. |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2226 Location: USA
|
|
|
|
All records created by your SQL query are of the same size.
1) calculate the exact number of bytes created by the query (r_size)
2) try to use //SYSREC00 DD …RECFM=FB,LRECL=r_size,…
3) try to use //SYSREC00 DD …RECFM=VB,LRECL=(r_size+4),…
#2 should work.
#3 may not work.
If so, you need to use intermediate temp DD:
Code: |
//SYSREC00 DD DSN=&&TEMPDATA,RECFM=FB,LRECL=r_size, |
and in the next step convert RECFM=FB to RECFM=VB using any utility, from IEBGENER to SORT, or others. |
|
Back to top |
|
 |
Rohit Umarjikar
Global Moderator

Joined: 21 Sep 2010 Posts: 3099 Location: NYC,USA
|
|
|
|
I suggest to write a Program and done with it when it comes to using functions in SELECT. Easy to write and maintain for future changes. |
|
Back to top |
|
 |
Taranprietsingh
New User
Joined: 25 Nov 2021 Posts: 10 Location: UK
|
|
|
|
Thank you Sergey. I did use the temp dataset and then used the SORT to convert the temp FB file into VB and using VLTRIM to remove the trailing spaces. Like below: ( please note it worked for a few codes successfully but i wonder for below code, the SORT step is not removing trailing spaces from the file, please help)
000011 //UNLOAD EXEC PGM=IKJEFT01
000012 //SYSTSPRT DD SYSOUT=*
000013 //SYSPRINT DD SYSOUT=*
000014 //SYSUDUMP DD SYSOUT=*
000015 //SYSTSIN DD *
000016 DSN SYSTEM(DB2P)
000017 RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
000018 LIB('DB2P.LOADLIB') PARM('SQL')
000019 END
000020 /*
000021 //SYSPUNCH DD DUMMY
000022 //SYSREC00 DD DSN=&&TEMPDATA,
000023 // DISP=(,CATLG,DELETE),
000024 // SPACE=(CYL,(5,5),RLSE),UNIT=(3390,30)
000025 //SYSIN DD *
000026 SELECT CHAR(STORE_NUMBER ||','||
000027 ITEM_CODE ||',"' ||
000028 TO_CHAR((DMD_FC_START_DATE + (7 - DAYOFWEEK(DMD_FC_START_DATE)) DAYS),
000029 'YYYY-MM-DD')||'",'||
000030 AH_WK_DMD_FC ||',"'||
000031 TO_CHAR(DATE_INSERTED,'YYYY-MM-DD')||'","'||
000032 VALUE(TO_CHAR(UPDATE_TIMESTAMP,'YYYY-MM-DD HH:MM:SS'),
000033 '1900-01-01 00:00:00') ||',"'||
000034 VALUE(PURGE_FLAG,' ') ||'","'||
000035 VALUE(REVIEW_FLAG,' ') ||'","'||
000036 TO_CHAR(CURRENT DATE,'YYYY-MM-DD') ||' '||
000037 CHAR(CURRENT TIME,JIS)||'"')
000038 FROM OPCSUB.MAVAHFCT
000039 WHERE DMD_FC_START_DATE >= (CURRENT DATE - 20 DAYS)
000040 FETCH FIRST 100 ROWS ONLY;
000041 /*
000042 //SORTFL EXEC PGM=SORT
000043 //SORTIN DD DSN=&&TEMPDATA,DISP=OLD
000044 //SORTOUT DD DSN=DEV@@ID.SFOCUS.AZUAHFCT.CONV.SUP246,
000045 // DISP=(NEW,CATLG,DELETE),
000046 // SPACE=(CYL,(5,5),RLSE),UNIT=(3390,30)
000047 //SYSOUT DD SYSOUT=*
000048 //SYSIN DD *
000049 OPTION COPY
000050 OUTFIL FNAMES=SORTOUT,VLTRIM=C' '
000051 /*
The output file - DEV@@ID.SFOCUS.AZUAHFCT.CONV.SUP246 is a VB file (which is good) but trailing spaces are not being removed even when I am using VLTRIM which i have used for other codes as well. Please help. Thanks. |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2226 Location: USA
|
|
|
|
There is not a minor sign that you planned to produce RECFM=VB result…
OUTFIL FTOV,…
Maybe, you’ll need to add extra:
//SORTOUT DD …,LRECL=max_expected_record,… |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2226 Location: USA
|
|
|
|
Please, format your samples like this:
Code: |
000011 //UNLOAD EXEC PGM=IKJEFT01
000012 //SYSTSPRT DD SYSOUT=*
000013 //SYSPRINT DD SYSOUT=*
000014 //SYSUDUMP DD SYSOUT=*
000015 //SYSTSIN DD *
000016 DSN SYSTEM(DB2P)
000017 RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
000018 LIB('DB2P.LOADLIB') PARM('SQL')
000019 END
000020 /*
000021 //SYSPUNCH DD DUMMY
000022 //SYSREC00 DD DSN=&&TEMPDATA,
000023 // DISP=(,CATLG,DELETE),
000024 // SPACE=(CYL,(5,5),RLSE),UNIT=(3390,30)
000025 //SYSIN DD *
000026 SELECT CHAR(STORE_NUMBER ||','||
000027 ITEM_CODE ||',"' ||
000028 TO_CHAR((DMD_FC_START_DATE + (7 - DAYOFWEEK(DMD_FC_START_DATE)) DAYS),
000029 'YYYY-MM-DD')||'",'||
000030 AH_WK_DMD_FC ||',"'||
000031 TO_CHAR(DATE_INSERTED,'YYYY-MM-DD')||'","'||
000032 VALUE(TO_CHAR(UPDATE_TIMESTAMP,'YYYY-MM-DD HH:MM:SS'),
000033 '1900-01-01 00:00:00') ||',"'||
000034 VALUE(PURGE_FLAG,' ') ||'","'||
000035 VALUE(REVIEW_FLAG,' ') ||'","'||
000036 TO_CHAR(CURRENT DATE,'YYYY-MM-DD') ||' '||
000037 CHAR(CURRENT TIME,JIS)||'"')
000038 FROM OPCSUB.MAVAHFCT
000039 WHERE DMD_FC_START_DATE >= (CURRENT DATE - 20 DAYS)
000040 FETCH FIRST 100 ROWS ONLY;
000041 /*
000042 //SORTFL EXEC PGM=SORT
000043 //SORTIN DD DSN=&&TEMPDATA,DISP=OLD
000044 //SORTOUT DD DSN=DEV@@ID.SFOCUS.AZUAHFCT.CONV.SUP246,
000045 // DISP=(NEW,CATLG,DELETE),
000046 // SPACE=(CYL,(5,5),RLSE),UNIT=(3390,30)
000047 //SYSOUT DD SYSOUT=*
000048 //SYSIN DD *
000049 OPTION COPY
000050 OUTFIL FNAMES=SORTOUT,VLTRIM=C' '
000051 /*
|
|
|
Back to top |
|
 |
Taranprietsingh
New User
Joined: 25 Nov 2021 Posts: 10 Location: UK
|
|
|
|
Thank you so much Sergey for prompt reply.
The thing is for all the codes, I have put that SORT step, which takes the FB temp file and using the FTOV and VLTRIM, its producing the VB file without trailing spaces perfectly. ( I did not had to use the LRECL).
Also, I cannot use the LRECL as records inside the final output file ( file created in the SORT step) are variable in length. Say if 1 record is of 80 LRCEL, another record might be 82, and so on. So do u think SORT would work ok?
Also, one more observation, I tried to run my query by removing this field and then its producing a perfect VB file without trailing spaces. Can you please see what could be the reason. Please help. Thanks.
I removed this field from the query
Code: |
CHAR((DMD_FC_START_DATE + (7 - DAYOFWEEK(DMD_FC_START_DATE)) DAYS)
,ISO) ||'",'|| |
Also, I am sorry for the code thing, I am new so not sure how to paste the code the way you are mentioning. [/code] |
|
Back to top |
|
 |
Taranprietsingh
New User
Joined: 25 Nov 2021 Posts: 10 Location: UK
|
|
|
|
Please do not be confused.
The field that I am talking about is
Code: |
TO_CHAR((DMD_FC_START_DATE + (7 - DAYOFWEEK(DMD_FC_START_DATE)) DAYS),
'YYYY-MM-DD')||'",'|| |
Which I also tried taking as
Code: |
CHAR((DMD_FC_START_DATE + (7 - DAYOFWEEK(DMD_FC_START_DATE)) DAYS)
,ISO) ||'",'|| |
File gets produced with correct format and data is ok. Only issue is trailing spaces are not being removed, which is really peculiar, as all other codes before this are working ok with that SORT Step.
So when I tried to run my code after removing this particular field (that I have pasted above), then my SORT output final file is without trailing spaces and perfect. Please help. Thanks. |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2226 Location: USA
|
|
|
|
Use
Code: |
OUTFIL FTOV,BUILD=(…full_record,SQZ=(SHIFT=LEFT,MID=C’ ‘)) |
|
|
Back to top |
|
 |
Taranprietsingh
New User
Joined: 25 Nov 2021 Posts: 10 Location: UK
|
|
|
|
sergeyken wrote: |
There is not a minor sign that you planned to produce RECFM=VB result…
OUTFIL FTOV,…
Maybe, you’ll need to add extra:
//SORTOUT DD …,LRECL=max_expected_record,… |
Sorry for that. I think I did not paste the correct SORT step
I am using below SORT card, and thats why I am wondering why it is still not removing the extra trailing spaces at the end of each record in the file.
Code: |
000043 //SORTFL EXEC PGM=SORT
000044 //SORTIN DD DSN=&&TEMPDATA,DISP=OLD
000045 //SORTOUT DD DSN=DEV@@ID.SFOCUS.AZUAHFCT.CONV.SUP246,
000046 // DISP=(NEW,CATLG,DELETE),
000047 // SPACE=(CYL,(5,5),RLSE),UNIT=(3390,30)
000048 //SYSOUT DD SYSOUT=*
000049 //SYSIN DD *
000050 OPTION COPY
000051 OUTFIL FNAMES=SORTOUT,FTOV,VLTRIM=C' '
000052 /* |
Also, I tried using the SORT card that you had suggested, I am not sure if those are 3 dots before Full_record. Could you please suggest. Its giving me syntax error. Many thanks in advance. I really appreciate your prompt replies. Thank you for being patient. Regards. Taran |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2226 Location: USA
|
|
|
|
Everything is working when coded properly:
Code: |
//SORT EXEC PGM=SYNCSORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
AAAAAAAAAAAAAAAA
BBBBBBBBB
CCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
EEEEEEEEEEEEEEEEEEEEEEE
FFFFFFFFFFFFFFFFFFF
G
//*
//SORTOUT DD SYSOUT=*
//SORTOUTF DD DISP=(NEW,KEEP),SPACE=(TRK,(10,10)),
// DSN=&SYSUID..SORTOUT
//*
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=(SORTOUT,SORTOUTF),
FTOV,
VLTRIM=C' '
END
//* |
Code: |
AAAAAAAAAAAAAAAA
CCCCCCCCCCCCCCCC
1111111111111111
----------------------------------------
BBBBBBBBB
CCCCCCCCC
222222222
----------------------------------------
CCC
CCC
333
----------------------------------------
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
4444444444444444444444444444444
----------------------------------------
EEEEEEEEEEEEEEEEEEEEEEE
CCCCCCCCCCCCCCCCCCCCCCC
55555555555555555555555
----------------------------------------
FFFFFFFFFFFFFFFFFFF
CCCCCCCCCCCCCCCCCCC
6666666666666666666
---------------------
G
C
7
--------------------- |
P.S.
Use BROWSE to verify the content of your resulting dataset. Using VIEW may extend your records on the screen with blanks not existing in the dataset! |
|
Back to top |
|
 |
Taranprietsingh
New User
Joined: 25 Nov 2021 Posts: 10 Location: UK
|
|
|
|
Thank you so much for prompt reply.
I have a few queries on your suggestion. (I haven't tried it yet)
1. The sort code that I have shared (which I am using for all the jobs) is working OK except a few. Which is surprising. One thing I noticed, which I want to share with you, if the tempdata file has LRCEL of 255 or less, then my SORT code works ok and converts the file into VB and removes the right trailing spaces. But when tempdata file has LRCEL of 256, then it only converts it into VB but does not removes the trailing spaces. Any thoughts on that please? what is going wrong in that case?
2. Do I need to use SYNCSORT for my SORT code? The way you have suggested, using SORTOUT and SORTOUTF?
Many many thanks for your support and help. Really appreciated. Regards. Taran |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2226 Location: USA
|
|
|
|
Taranprietsingh wrote: |
2. Do I need to use SYNCSORT for my SORT code? The way you have suggested, using SORTOUT and SORTOUTF? |
Those two files have been used for testing purposes: the output goes in parallel to SYSOUT, and to the output dataset.
I do not understand the rest of your questions. I gave you 100% working sample; try to understand it, and to adjust it for your needs. |
|
Back to top |
|
 |
Rohit Umarjikar
Global Moderator

Joined: 21 Sep 2010 Posts: 3099 Location: NYC,USA
|
|
|
|
I am surprised to see the issue still not resolved after so many weeks when one program of (1-2 hour max ) could ease the ask.. Good Luck ! |
|
Back to top |
|
 |
Taranprietsingh
New User
Joined: 25 Nov 2021 Posts: 10 Location: UK
|
|
|
|
Hello,
I am using browse mode to open the file and check for the trailing spaces.
Also, I used the SORT code provided by you
Code: |
//SORTFL EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&TEMPDATA,DISP=OLD
//SORTOUT DD SYSOUT=*
//SORTOUTF DD DSN=DEV@@ID.SFOCUS.AZUAHFCT.CONV.SUP246,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,5),RLSE),UNIT=(3390,30)
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=(SORTOUT,SORTOUTF),
FTOV,
VLTRIM=C' '
END
/* |
Still trailing spaces are not being removed from the output file at the end of a record. see below
Code: |
BROWSE DEV@@ID.SFOCUS.AZUAHFCT.CONV.SUP246
Command ===>
********************************* Top of Data **
-----------------------------------------------
5 16.51.16"
F4FF4FF4FF74444444444444444444444444444444444444
5016B51B16F0000000000000000000000000000000000000
-----------------------------------------------
1-25 16.51.16"
F6FF4FF4FF4FF74444444444444444444444444444444444
1025016B51B16F0000000000000000000000000000000000
----------------------------------------------- |
Also, as I mentioned earlier, I am using CHAR scalar function as a whole to run my query. ( record length for CHAR scalar function is 255)
Code: |
SELECT CHAR(STORE_NUMBER ||','||
ITEM_CODE ||',"' ||
CHAR((DMD_FC_START_DATE + (7 - DAYOFWEEK(DMD_FC_START_DATE)) DAYS)
,ISO) ||'",'||
AH_WK_DMD_FC ||',"'||
TO_CHAR(DATE_INSERTED,'YYYY-MM-DD')||'","'||
VALUE(TO_CHAR(UPDATE_TIMESTAMP,'YYYY-MM-DD HH24:MM:SS'),
'1900-01-01 00:00:00') ||',"'||
VALUE(PURGE_FLAG,' ') ||'","'||
VALUE(REVIEW_FLAG,' ') ||'","'||
TO_CHAR(CURRENT DATE,'YYYY-MM-DD') ||' '||
CHAR(CURRENT TIME,ISO)||'"')
FROM OPCSUB.MAVAHFCT
WHERE DMD_FC_START_DATE >= (CURRENT DATE - 20 DAYS)
FOR FETCH ONLY; |
With above query, my temp data file is of 256 LRECL (otherwise mostly it is 255)
And I tried bit of hit and trial, and found that if I remove below date calculation field from the above query, then file is 255 LRECL and trailing spaces are successfully removed.
Code: |
CHAR((DMD_FC_START_DATE + (7 - DAYOFWEEK(DMD_FC_START_DATE)) DAYS)
,ISO) ||'",'|| |
Please help. Thanks. |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2226 Location: USA
|
|
|
|
Are you sure there is nothing at the end of your input records?
There are no such things as miracles. I have demonstrated you that VLTRIM parameter is working as expected. Analyze ALL your data, ALL your logs, ALL your messages, ALL your datasets (both input, and output!), and ALL your code more carefully. We cannot guess what else is involved into your issue; you even hide the full record content from the forum!
As the first step: copy and paste my working example to be sure it is really working in your environment (I am sure it is). Next, try step by step adding your own data - first as input stream, then - as a separate dataset, and VERIFY THE CONTENT OF YOUR INPUT when running!
Before combining together your SQL query, and SORT step, get really familiar how the SORT step works, using some verified input test data, rather than those received from DB2. When all SORT issues are resolved, then start to combine both of your steps together, carefully verifying all your data at each step! |
|
Back to top |
|
 |
|
|