Page 1 of 1

DSNTIAUL and DB2 scalar functions

PostPosted: Thu Nov 25, 2021 1:43 am
by Taranprietsingh
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"
F67CD767CCECCCE4CCDCCCE476FFFFFFFF6FFFFFFFF6F4FF67CCECCCE767C744444444444444444
1BF93FBF3931942B25556930FB20211117B20211130B2B00BF3931942FBF2F00000000000000000
------------------------------------------------------------------------------
1,"IL","CITAIDS.ELOFFER ",20211201,20211207,1.10,"CITAIDS","B"
F67CD767CCECCCE4CDDCCCD476FFFFFFFF6FFFFFFFF6F4FF67CCECCCE767C744444444444444444
1BF93FBF3931942B53666590FB20211201B20211207B1B10BF3931942FBF2F00000000000000000

Re: DSNTIAUL and DB2 scalar functions

PostPosted: Fri Nov 26, 2021 9:23 pm
by sergeyken
1) do not post the same question in two different parts of the same forum.

2) first of all: learn how to use CODE TAGS when presenting your samples of code