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:
