DSNTIAUL and DB2 scalar functions



IBM's flagship relational database management system

DSNTIAUL and DB2 scalar functions

Postby Taranprietsingh » Thu Nov 25, 2021 1:43 am

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
Taranprietsingh
 
Posts: 1
Joined: Thu Nov 25, 2021 1:28 am
Has thanked: 0 time
Been thanked: 0 time

Re: DSNTIAUL and DB2 scalar functions

Postby sergeyken » Fri Nov 26, 2021 9:23 pm

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
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 408
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 6 times
Been thanked: 40 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post