zOs/TX/PER05RIX

/* rexx */
parse arg vw
if vw = '' then
    vw = 'vPer05UnionInt'
call sqlConnect dbaf
dv = 0530000000
dt = 0602999999
say 'selecting' vw '.................'
src = 'select count(*), min(ix), max(ix)',
            "from A540769."vw "where ix between ? and ?"
call sqlExec 'prepare s1 from :src'
call sqlExec 'describe input s1 into :M.SQL.1.I'
m.sql.1.I.1.sqlData = dv
m.sql.1.I.1.sqlInd  = 0
m.sql.1.I.2.sqlData = dt
m.sql.1.I.2.sqlInd  = 0
call sqlExec 'open c1 using descriptor :M.SQL.1.I'
call sqlExec 'fetch c1 into :cnt,:min :ii,:max :ia'
say 'fetched cnt' cnt':' min '-' max
call sqlExec 'close c1'
ggSqlStmt = 'disconnect'
address dsnRexx ggSqlStmt
exit

/*--- connect to the db2 subsystem ggSys -----------------------------*/
sqlConnect: procedure expose m.
parse arg ggSys
    address tso "SUBCOM DSNREXX"
    if rc <> 0 then do
       sRc = RXSUBCOM('ADD','DSNREXX','DSNREXX') /* ADD HOST CMD ENV  */
       if sRc <> 0 then
           call err 'rc' sRc 'from RXSUBCOM(ADD, DSNREXX, DSNREXX)'
       end
    ggSqlStmt = 'connect' ggSys
    address dsnRexx ggSqlStmt
    if rc <> 0 then
        call err sqlMsg()
    return
endProcedure sqlConnect

/*--- do an sqlExec, report a bad sqlCode with an error -------------*/
sqlExec: /* no procedure, to keep variables sql... */
    parse arg ggSqlStmt, ggRet
    address dsnRexx 'execSql' ggSqlStmt
    if rc = 0 then
        return 0
    if pos('*', ggRet) < 0 | wordPos(sqlCode, ggRet) > 0 then
        return sqlCode
    else if sqlCode \= 0 then
        call err sqlmsg()
    say sqlmsg()
    return 0
endSubroutine sqlExec

/*--- return an sql error message ------------------------------------*/
sqlMsg: /* no procedure, to keep variables sql... */
    signal on syntax name sqlMsgOnSyntax
    ggRes = 'sqlCode' sqlCodeT(sqlCode, sqlErrMc, sqlwarn.0':' ,
       || sqlwarn.1||sqlwarn.2||sqlwarn.3||sqlwarn.4||sqlwarn.5',',
       || sqlwarn.6||sqlwarn.7||sqlwarn.8||sqlwarn.9||sqlwarn.10)
    if 0 then
      sqlMsgOnSyntax: do
        ggRes = 'sqlCode' sqlCode translate(sqlErrMc, ',', 'ff'x),
                '<<rexx sqlCodeT not found or syntax>>\nwarnings'
        do ggX=0 to 10
            if sqlWarn.ggx <> '' then
                ggRes = ggRes ggx'='sqlWarn.ggx
            end
        end
    signal off syntax
    ggRes = ggRes'\nstate' sqlState'\nstmt = ' ggSqlStmt
    ggPref = '\nwith'
    ggXX = pos(':', ggSqlStmt)+1
    do 12 while ggXX > 1
        ggYY = verify(ggSqlStmt, ' ,:+-*/&%?|()[]', 'm', ggXX)
        if ggYY < 1 then
            ggYY = length(ggSqlStmt) + 1
        ggVar = substr(ggSqlStmt, ggXX, ggYY - ggXX)
        if ggVar <> '' then do
            ggRes = ggRes || ggPref ggVar '=' value(ggVar)
            ggPref = '\n    '
            end
        ggXX = pos(':', ggSqlStmt, ggYY+1) + 1
        end
    return  ggRes
endProcedure sqlMsg

/*--- issue an errorMessage and exit ---------------------------------*/
err:
parse arg ggTxt
    ggTxt =  'error:' ggTxt
    bx = 1
    do while  bx <= length(ggTxt)
            ex = pos('\n', ggTxt, bx)
        if ex < 1 then
            ex = length(ggTxt)+1
        say substr(ggTxt, bx, ex-bx)
        bx = ex+2
        end
    say 'error exit 12'
    exit 12