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;
/*
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.
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.
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:
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)
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.
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.
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.
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
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!
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
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.
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!
