Wednesday, November 4, 2009

SQL collections

SELECT *
FROM SYSIBM.SYScolumns where tbname in ( select name from sysibm.systables
WHERE CREATOR ='SIDZ1008' and type = 'T' ) ;

select * from sysibm.syscolumns where TBNAME like '%QRTRLY_COLLECTNS%' ;


select * from SIDz1201.vQRTRLY_COLLECTNS
fetch first 10 rows only;

Queries
SELECT *
FROM SYSIBM.SYSINDEXES
WHERE CREATOR LIKE 'SIDZ1%'
AND TBNAME = 'TCASE'
AND UNIQUERULE = 'P'
WITH UR
FETCH FIRST 2000 ROWS ONLY;






insert into sidc3006.VEWO_HOLD_QUEUE( select IRN , ENTRY_TS, date(entry_ts) + 20 days
from sidc3002.VAP_EMPLOYER_REPLY where irn in (400031231 ,
405009120 ,
406008707 ,
406008712 ,
406008750 ,
406009074 ,
406009167 ,
407008436 ,
408008505 ,
410009029 ,
412008749 ,
412008751 ,
413008669 ,
418009859 ,
443009801 ,
444009581 ,
454009774 ,
458009203 ,
459009204
) )


select * from sidc3006.VEWO_HOLD_QUEUE





select * from sidc3008.vescrow_hist where case_num = 11947 ;
select * from sidc3008.vfunds_in_escrow where case_num = 11947 ;

select * from sidc3008.vaccount where case_num = 11947 ;


insert into sidc3008.vfunds_in_escrow( select ESCROW_TYPE, RCPT_NUM, ' ' , CREATION_DT, ESCROW_AMT , 0 , JURISD, PAYEE_CL_IND, PRV_RET_DISBUR_IND, ' ', DNLOAD_NUM, FIPS_CD, ' ', ACCT_TYPE, CRTORD_NUM, CASE_NUM, IRN, CRTORD_FIPS_CD, ESCROW_ORDER, 0, APPROVE_DT, ' ', AU_NUM from sidc3008.vescrow_hist where case_num = 11947 and acct_type = 'CC' ) ;


INSERT MANY ROWS
EXEC SQL
INSERT INTO DSN8810.ACT
(ACTNO, ACTKWD, ACTDESC)
VALUES (:HVA1, :HVA2, :HVA3 :IVA3)
FOR :NUM-ROWS ROWS
END-EXEC.




select distinct a.ap_irn from sidz1004.vcase a , SIDz1002.VAP_EMPLOYER_REPLy b
where a.ap_irn <> b.irn
and a.case_type = 'NI'
and a.case_status = 'ATV'
and a.case_num > 100
fetch first 20 rows only





select a.ap_irn , count(*) from sidz1004.vcase a , SIDz1002.VAP_EMPLOYER_REPLy b
where a.ap_irn = b.irn
and a.case_type = 'NI'
and a.case_status = 'ATV'
and a.case_num > 100

group by a.ap_irn ;

;


select * from sidst004.vcase a
where a.ap_irn = 491009280 ;


select case_num , CASE_ACTLG_DT, userid , count(*) from SIDz1004.Vcase_Action_log
where case_num > 100
and case_actlg_msg1 like 'NEW AP EMPLOYER DATA FROM%'

group by case_num , CASE_ACTLG_DT, userid
having count(*) > 1










select * from sidc3008.vescrow_hist where case_num = 11947 ;
select case_num , CASE_ACTLG_DT, userid , count(*) from SIDz1004.Vcase_Action_log
where case_num > 100
and case_actlg_msg1 like 'NEW AP EMPLOYER DATA FROM%'

group by case_num , CASE_ACTLG_DT, userid
having count(*) > 1










select * from sidc3008.vescrow_hist where case_num = 11947 ;
select * from sidc3008.vfunds_in_escrow where case_num = 11947 ;

select * from sidc3008.vfunds_in_escrow FETCH FIRST 200 ROWS only ;





Insert into sidc3004.vpip_agreemnt(
CASE_NUM, APLN_SEQ_NUM, APLN_DT, APLN_DECISION_DT,
APLN_DECISION_CD, SIGNED_DT, PGM_START_DT, ARREAR_START_AMT,
CMPLD_12MONTH_IND, CMPLD_12MONTH_DT, ARREAR_12MONTH_AMT,
CMPLD_24MONTH_IND, CMPLD_24MONTH_DT, ARREAR_24MONTH_AMT,
EXIT_REASON_CD, EXIT_DT, LAST_BREACH_DT, MONTHS_PROC_NUM,
LAST_UPD_USER_ID, LAST_UPD_PGM_NAM, LAST_UPD_TS )

select
distinct a.case_num ,
APLN_SEQ_NUM, APLN_DT, APLN_DECISION_DT,
APLN_DECISION_CD, SIGNED_DT, PGM_START_DT, ARREAR_START_AMT,
CMPLD_12MONTH_IND, CMPLD_12MONTH_DT, ARREAR_12MONTH_AMT,
CMPLD_24MONTH_IND, CMPLD_24MONTH_DT, ARREAR_24MONTH_AMT,
EXIT_REASON_CD, EXIT_DT, LAST_BREACH_DT, MONTHS_PROC_NUM,
LAST_UPD_USER_ID, LAST_UPD_PGM_NAM, LAST_UPD_TS

from sidc3008.vaccount a , sidc3004.vpip_agreemnt b
where a.crtord_eff_dt between 19971100 and 20071111
and b.case_num = 11057
and a.case_num between 11057 and 110000
--fetch first 10 ROWS only

select
(select distinct case_num "case_num" from sidc3008.vaccount
where crtord_eff_dt between 19971100 and 20071111
) ,

max(APLN_SEQ_NUM) ,
max( APLN_DT) ,
max( APLN_DECISION_DT) ,
max(APLN_DECISION_CD) ,
max( SIGNED_DT) ,
max( PGM_START_DT) ,
max( ARREAR_START_AMT) ,
max(CMPLD_12MONTH_IND) ,
max( CMPLD_12MONTH_DT) ,
max( ARREAR_12MONTH_AMT) ,
max(CMPLD_24MONTH_IND) ,
max( CMPLD_24MONTH_DT) ,
max( ARREAR_24MONTH_AMT) ,
max(EXIT_REASON_CD) ,
max( EXIT_DT) ,
max( LAST_BREACH_DT) ,
max( MONTHS_PROC_NUM) ,
max(LAST_UPD_USER_ID) ,
max( LAST_UPD_PGM_NAM) ,
max( LAST_UPD_TS)

from sidc3004.vpip_agreemnt where

sidc3004.vpip_agreemnt.case_num = 11057



apr-30

select * from SIDST002.VAP_EMPLOYER_REPLY where irn in ( 449008657, 418008765)
order by ENTRY_TS desc
fetch first 200 rows only;


select case_num, ap_irn from SIDST004.vcase where case_num in ( 59001161, 180011422, 11471)


410009029

select distinct a.tbl_id, a.codexn from sidz1010.vcodes a , sidz1010.vcodes b where a.CODEXN in ( '30' , '32' , '60' , '62' )
and b.TBL_ID = a.TBL_ID ;

select a.tbl_id , count(*) from sidz1010.vcodes a where a.CODEXN in ( '30' , '32' , '60' , '62' )
and exists ( select b.TBL_ID from sidz1010.vcodes b where b.TBL_ID = a.TBL_ID )
group by a.tbl_id order by 2 desc ;



select a.case_num , b.irn, a.case_status from sidst004.Vcase a , sidst002.Vabsent_parent b where
a.case_num in (990011281,640011900,490011717,820012388,50011322,220011070,210011983)
and a.ap_irn = b.irn




EXEC SQL
DECLARE PENDDRV_CSR INSENSITIVE SCROLL CURSOR
WITH ROWSET POSITIONING FOR
SELECT DISTINCT
T595.ID_DVN
,T595.NA_VENDOR
,T003.ID_INDV_DCN
,T003.NA_INDV_LAST ||' ,' || T003.NA_INDV_FIRST || ' '
|| T003.NA_INDV_MID || ' ' || T003.NA_INDV_SFX
,T680.TS_PNDPAYLI_PK
,COALESCE(CHAR(T680.TS_NOTES_FK), ' ')
,COALESCE(CHAR(T680.TS_PLACEMENT_FK), ' ')
,T680.NO_VENDOR_BILLING
,T680.AM_TOTAL_LINE_ITEM
,T680.CO_PROGRAM_AREA
,( SELECT LEFT(A18.TX_CODE_DESC, 100) FROM
SFCWT018_CODE_TBLE A18 WHERE
A18.CO_CODE_VALUE = T680.CO_PROGRAM_AREA
AND A18.DA_END IS NULL
AND A18.CO_CODE_TYPE = 'PROGRAMAREA' )
,T680.CO_FUND
,IFNULL(( SELECT LEFT(B18.TX_CODE_DESC, 100) FROM
SFCWT018_CODE_TBLE B18 WHERE
B18.CO_CODE_VALUE = T680.CO_FUND
AND B18.DA_END IS NULL
AND B18.CO_CODE_TYPE = 'FUNDCATEGORY' ), ' ' )
,T680.CO_SERVICE
,IFNULL(( SELECT MAX(left(T569.TX_DESCRIPTION,60)) FROM
SFCWT569_SRVAMDCD T569 WHERE
T569.CO_SVC_AMEND = T680.CO_SERVICE
AND T569.CO_PROGRAM_AREA =T680.CO_PROGRAM_AREA), ' ' )
,T680.DA_SERVICE_BEGIN
,T680.DA_SERVICE_END
,T680.AM_TOTAL_UNITS
,T680.CO_VENDOR_TYPE
,IFNULL(( SELECT LEFT(V18.TX_CODE_DESC, 50) FROM
SFCWT018_CODE_TBLE V18 WHERE
V18.CO_CODE_VALUE = T680.CO_VENDOR_TYPE
AND V18.DA_END IS NULL
AND V18.CO_CODE_TYPE = 'VENDORTYPE' ), ' ' )
,T680.CO_PAY_COUNTY
,T680.CO_PAY_OFFICE
,T680.CO_ERROR_TYPE
,T680.SW_DO_NOT_PAY
FROM
SFCWT595_VENDOR T595
,SFCWT003_INDV T003
,SFCWT680_PNDPAYLI T680
WHERE
T680.TS_INDV_FK = T003.TS_INDV_PK
AND T680.TS_VENDOR_FK = T595.TS_VENDOR_PK

AND ( T680.CO_PROGRAM_AREA = :T680-CO-PROGRAM-AREA
OR :T680-CO-PROGRAM-AREA = ' ' )
AND ( T680.CO_PAY_COUNTY =:T680-CO-PAY-COUNTY
OR :T680-CO-PAY-COUNTY = ' ' )
AND ( T680.CO_SERVICE = :T680-CO-SERVICE
OR :T680-CO-SERVICE = ' ' )
AND ( T680.DA_SERVICE_BEGIN >= :T680-DA-SERVICE-BEGIN )
* OR :T680-DA-SERVICE-BEGIN = '0000-00-00' )
AND ( T680.DA_SERVICE_END <= :T680-DA-SERVICE-END )
* OR :T680-DA-SERVICE-END = '9999-99-99' )

AND ( T003.ID_INDV_DCN = :T003-ID-INDV-DCN
OR :T003-ID-INDV-DCN = ' ' )

AND ( T595.ID_DVN = :T595-ID-DVN
OR :T595-ID-DVN = ' ' )

AND ( T680.SW_CAREERPRNT_PMT = :T680-SW-CAREERPRNT-PMT
OR :T680-SW-CAREERPRNT-PMT = ' ' )

AND ( T680.SW_CLIENT_PMT = :T680-SW-CLIENT-PMT
OR :T680-SW-CLIENT-PMT = ' ' )

AND ( T680.SW_VENDOR_PMT = :T680-SW-VENDOR-PMT
OR :T680-SW-VENDOR-PMT = ' ' )

END-EXEC
EXEC SQL
OPEN PENDDRV_CSR
END-EXEC
IF SQLCODE < 0
PERFORM 9900-SQL-ERROR
END-IF.
INITIALIZE WS-ROWSET-AREA

EXEC SQL
FETCH ROWSET STARTING AT
ABSOLUTE :WS-CSRPOS FROM PENDDRV_CSR
FOR :WS-RECS-TO-FETCH ROWS
INTO
:WS-RS-ID-DVN
,:WS-RS-VENDOR-NAME
,:WS-RS-ID-DCN
,:WS-RS-CLNT-NAME
,:WS-RS-PYMT-LN-PK
,:WS-RS-NOTES-FK
,:WS-RS-PYMT-LN-ITEM-PK
,:WS-RS-TRACKIN-NO
,:WS-RS-AM-LINE-ITEM
,:WS-RS-PROGRAM-AREA
,:WS-RS-PGM-DESC
,:WS-RS-CO-FUND
,:WS-RS-FUND-DESC
,:WS-RS-CO-SERV
,:WS-RS-SERV-DESC
,:WS-RS-DA-SERV-BEGIN
,:WS-RS-DA-SERV-END
,:WS-RS-NO-UNITS
,:WS-RS-VENDOR-TYPE
,:WS-RS-VEND-DESC
,:WS-RS-CO-PAY-COUNTY
,:WS-RS-CO-PAY-OFFICE
,:WS-RS-CO-TYPE
,:WS-RS-PYMNT-HIST-SW
END-EXEC
EVALUATE TRUE
WHEN SQLCODE < 0
PERFORM 9900-SQL-ERROR
WHEN OTHER
MOVE SQLERRD(3) TO WS-RECS-FETCHD
IF WS-RECS-FETCHD = WS-RECS-TO-FETCH
MOVE 'Y' TO FCWCP812-SHOW-NEXT
SUBTRACT 1 FROM WS-RECS-FETCHD
END-IF
END-EVALUATE

EXEC SQL
CLOSE PENDDRV_CSR
END-EXEC
IF SQLCODE < 0
PERFORM 9900-SQL-ERROR
END-IF.

INITIALIZE FCWCP812-ROWSET-DVNDCN
MOVE WS-PAGE-NO TO FCWCP812-PAGE-NUM
PERFORM VARYING WS-INDEX FROM +1 BY +1
UNTIL WS-INDEX > WS-RECS-FETCHD
OR WS-INDEX > 20
MOVE WS-RS-ID-DVN (WS-INDEX) TO
FCWCP812-RS-ID-DVN (WS-INDEX)
MOVE WS-RS-VENDOR-NAME (WS-INDEX) TO
FCWCP812-RS-VENDOR-NAME (WS-INDEX)
MOVE WS-RS-VEND-DESC (WS-INDEX) TO
FCWCP812-RS-VEND-DESC (WS-INDEX)
MOVE WS-RS-ID-DCN (WS-INDEX) TO
FCWCP812-RS-ID-DCN (WS-INDEX)
MOVE WS-RS-CLNT-NAME (WS-INDEX) TO
FCWCP812-RS-CLNT-NAME (WS-INDEX)
MOVE WS-RS-PYMT-LN-PK (WS-INDEX) TO
FCWCP812-RS-PYMT-LN-PK (WS-INDEX)
MOVE WS-RS-PYMT-LN-ITEM-PK(WS-INDEX) TO
FCWCP812-RS-PYMT-LN-ITEM-PK(WS-INDEX)
MOVE WS-RS-NOTES-FK (WS-INDEX) TO
FCWCP812-RS-NOTES-FK (WS-INDEX)
MOVE WS-RS-TRACKIN-NO (WS-INDEX) TO
FCWCP812-RS-TRACKIN-NO (WS-INDEX)

MOVE WS-RS-AM-LINE-ITEM (WS-INDEX) TO
FCWCP812-RS-AM-LINE-ITEM (WS-INDEX)
MOVE WS-RS-PGM-DESC (WS-INDEX) TO
FCWCP812-RS-PGM-DESC (WS-INDEX)
MOVE WS-RS-FUND-DESC (WS-INDEX) TO
FCWCP812-RS-FUND-DESC (WS-INDEX)
MOVE WS-RS-FUND-DESC (WS-INDEX) TO
FCWCP812-RS-FUND-DESC (WS-INDEX)
MOVE WS-RS-SERV-DESC (WS-INDEX) TO
FCWCP812-RS-SERV-DESC (WS-INDEX)
MOVE WS-RS-DA-SERV-BEGIN(WS-INDEX) TO
FCWCP812-RS-DA-SERV-BEGIN (WS-INDEX)
MOVE WS-RS-DA-SERV-END (WS-INDEX) TO
FCWCP812-RS-DA-SERV-END (WS-INDEX)
MOVE WS-RS-NO-UNITS (WS-INDEX) TO
FCWCP812-RS-NO-UNITS (WS-INDEX)
IF WS-RS-CO-PAY-COUNTY(WS-INDEX) > SPACES
INITIALIZE FCWCP462-PARMS
MOVE WS-RS-CO-PAY-COUNTY(WS-INDEX)
TO FCWCP462-CO-FIPS-IN
MOVE WS-RS-CO-PAY-OFFICE(WS-INDEX)
TO FCWCP462-CO-OFFICE-IN
SET FCWCP462-ACTION-CNTY-OFF TO TRUE
EXEC CICS
LINK PROGRAM('FCWSC462')
COMMAREA(FCWCP462-PARMS)
LENGTH(LENGTH OF FCWCP462-PARMS)
END-EXEC
IF FCWCP462-ERRMSG <= SPACES
STRING FCWCP462-COUNTY-NAME DELIMITED BY ' '
' - ' DELIMITED BY SIZE
FCWCP462-CO-FIPS-IN DELIMITED BY SIZE
'/' DELIMITED BY SIZE
FCWCP462-CO-OFFICE-IN DELIMITED BY SIZE
'(' DELIMITED BY SIZE
FCWCP462-NA-OFFICE DELIMITED BY ' '
')' DELIMITED BY SIZE
INTO FCWCP812-RS-CNTY-DESC (WS-INDEX)
END-STRING
ELSE
MOVE SPACES TO FCWCP812-RS-CNTY-DESC (WS-INDEX)
MOVE 'LINK TO FCWSC462 ERROR' TO FCWCP812-ERRMSG
END-IF
END-IF
MOVE WS-RS-CNTY-DESC (WS-INDEX) TO
FCWCP812-RS-CNTY-DESC (WS-INDEX)
MOVE WS-RS-CO-TYPE (WS-INDEX) TO
FCWCP812-RS-CO-TYPE (WS-INDEX)
MOVE WS-RS-PYMNT-HIST-SW (WS-INDEX) TO
FCWCP812-RS-PYMNT-HIST-SW (WS-INDEX)
END-PERFORM
.
5100-PAYMENT-DTL-EXIT.
EXIT.


http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.relg/fmlt.htm


Example: The following statement declares a row set cursor:

EXEC SQL
DECLARE C1 CURSOR
WITH ROWSET POSITIONING FOR
SELECT EMPNO, LASTNAME, SALARY
FROM DSN8810.EMP
END-EXEC.

To tell DB2 that you are ready to process the first row set of the result table, execute the OPEN statement in your program. DB2 then uses the SELECT statement within the DECLARE CURSOR statement to identify the rows in the result table.
Using a multiple-row FETCH statement with host variable arrays

When your program executes a FETCH statement with the ROWSET keyword, the cursor is positioned on a row set in the result table. That row set is called the current row set. Declare the dimension of each of the host variable arrays to be greater than or equal to the number of rows that are to be retrieved.

Example: The following FETCH statement retrieves 20 rows into host variable arrays that are declared in your program:

EXEC SQL
FETCH NEXT ROWSET FROM C1
FOR 20 ROWS
INTO :HVA-EMPNO, :HVA-LASTNAME, :HVA-SALARY :INDA-SALARY
END-EXEC.

Using a multiple-row FETCH statement with a descriptor

Suppose that you want to dynamically allocate the necessary storage for the arrays of column values that are to be retrieved from the employee table. You must do the following steps:

1. Declare an SQLDA structure.
2. Dynamically allocate the SQLDA and the necessary arrays for the column values.
3. Set the fields in the SQLDA for the column values that are to be retrieved.
4. Open the cursor.
5. Fetch the rows.

After allocating the SQLDA and the necessary arrays for the column values, you must set the fields in the SQLDA.

Example: After the OPEN statement, the program fetches the next row set by using the following statement:

EXEC SQL
FETCH NEXT ROWSET FROM C1
FOR 20 ROWS
USING DESCRIPTOR :outsqlda;

The USING clause of the FETCH statement names the SQLDA that describes the columns that are to be retrieved.
Using row-set positioned UPDATE statements

After your program executes a FETCH statement to establish the current row set, you can use a positioned UPDATE statement with either of the following clauses:

* WHERE CURRENT OF cursor-name to update:
o a single row if the cursor is on a single row
o all the rows of a row set if the cursor is on a row set
* WHERE CURRENT OF cursor-name FOR ROW n OF ROWSET to update only row n of the current row set

Updating all rows of the current row set: The following positioned UPDATE statement uses the WHERE CURRENT OF clause:

EXEC SQL
UPDATE DSN8810.EMP
SET SALARY = 50000
WHERE CURRENT OF C1
END-EXEC.

When the UPDATE statement is executed, the cursor must be positioned on a row or row set of the result table. If the cursor is positioned on a row, that row is updated. If the cursor is positioned on a row set, all of the rows in the row set are updated.

Updating a specific row of the current row set: The following positioned UPDATE statement uses the WHERE CURRENT OF cursor FOR ROW n OF ROWSET clause:

EXEC SQL
UPDATE DSN8810.EMP
SET SALARY = 50000
WHERE CURRENT OF C1 FOR ROW 5 OF ROWSET
END-EXEC.

When the UPDATE statement is executed, the cursor must be positioned on a row set of the result table. The specified row (in the example, row 5) of the current row set is updated.
Using row-set positioned DELETE statements

After your program executes a FETCH statement to establish the current row set, you can use a positioned DELETE statement with either of the following clauses:

* WHERE CURRENT OF cursor-name to delete:
o a single row if the cursor is on a single row
o all the rows of a row set if the cursor is on a row set
* WHERE CURRENT OF cursor-name FOR ROW n OF ROWSET to delete only row n of the current row set

Deleting all rows of the current row set: The following positioned DELETE statement uses the WHERE CURRENT OF clause:

EXEC SQL
DELETE FROM DSN8810.EMP
WHERE CURRENT OF C1
END-EXEC.

When the DELETE statement is executed, the cursor must be positioned on a row or row set of the result table. If the cursor is positioned on a row, that row is deleted, and the cursor is positioned before the next row of its result table. If the cursor is positioned on a row set, all of the rows in the row set are deleted, and the cursor is positioned before the next row set of its result table.

Deleting a single row of the current row set: The following positioned DELETE statement uses the WHERE CURRENT OF cursor FOR ROW n OF ROWSET clause:

EXEC SQL
DELETE FROM DSN8810.EMP
WHERE CURRENT OF C1 FOR ROW 5 OF ROWSET
END-EXEC.

When the DELETE statement is executed, the cursor must be positioned on a row set of the result table. The specified row of the current row set is deleted, and the cursor remains positioned on that row set. The deleted row (in the example, row 5 of the row set) cannot be retrieved or updated.

OCSE34 DUPLICATE PROBLEM FIX :


SELECT A.CASE_NUM, A.JURISD, A.CRTORD_FIPS_CD, A.CRTORD_NUM,
A.ACCT_TYPE, A.RCPT_NUM, A.CASE_TYPE, A.CASE_SUBTYPE,
A.APPLIED_AMT, A.DBCR_CD, A.ENTRY_DT,
A.AU_NUM, A.ESCROW_GEN_FLG, A.POST_DT,

case
when 1 = ( select count(*) FROM SIDRS035.VDISB_ESCROW_WORK r

WHERE r.CASE_NUM = A.CASE_NUM
AND r.JURISD = A.JURISD
AND r.CRTORD_FIPS_CD = A.CRTORD_FIPS_CD
AND r.CRTORD_NUM = A.CRTORD_NUM
AND r.ACCT_TYPE = A.ACCT_TYPE
AND r.RCPT_NUM = A.RCPT_NUM )

then

(SELECT digits(C.CHECK_NUM) || char(C.CHECK_DT) || CHAR(C.CREATION_DT)
FROM SIDRS035.VDISB_ESCROW_WORK C
WHERE C.CASE_NUM = A.CASE_NUM
AND C.JURISD = A.JURISD
AND C.CRTORD_FIPS_CD = A.CRTORD_FIPS_CD
AND C.CRTORD_NUM = A.CRTORD_NUM
AND C.ACCT_TYPE = A.ACCT_TYPE
AND C.RCPT_NUM = A.RCPT_NUM

)

else

(SELECT max(digits(m.CHECK_NUM) || char(m.CHECK_DT) || CHAR(m.CREATION_DT))
FROM SIDRS035.VDISB_ESCROW_WORK m
WHERE m.CASE_NUM = A.CASE_NUM
AND m.JURISD = A.JURISD
AND m.CRTORD_FIPS_CD = A.CRTORD_FIPS_CD
AND m.CRTORD_NUM = A.CRTORD_NUM
AND m.ACCT_TYPE = A.ACCT_TYPE
AND m.RCPT_NUM = A.RCPT_NUM
AND m.CREATION_DT = A.ENTRY_DT
)

end case

FROM
SIDRS008.VDISTRIBUTION_HIST A
where exists
( select 1
FROM SIDRS035.VDISB_ESCROW_WORK E
WHERE E.CASE_NUM = A.CASE_NUM
AND E.JURISD = A.JURISD
AND E.CRTORD_FIPS_CD = A.CRTORD_FIPS_CD
AND E.CRTORD_NUM = A.CRTORD_NUM
AND E.ACCT_TYPE = A.ACCT_TYPE
AND E.RCPT_NUM = A.RCPT_NUM
)

order by A.JURISD, A.RCPT_NUM, A.CASE_NUM
WITH UR;

REXX

SETUP

/* REXX */
ADDRESS TSO
"ALLOC FILE(SYSEXEC)
DA('SYSLOC.TSO.COMMON.EXEC.FB','DBDCLOC.TSO.S0F.EXEC.FB',
'SYS1.SISPEXEC','NDVR.EDVTOOLS.REXX','RSXRR3.ROMMY.EXEC')SHR REUSE"
ISPF



_________________________

exec 'rsxrr3.share.rexx(SETUP)' in the initial command b4 logon





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

KR-228* How to execute REXX codes

Addendum to the REXX crash course series.

4 ways to execute REXX codes

1.) Foreground execution

Prefix member name by EXEC within the member listing screen – the method we’ve been using so far.

2.) Another foreground method

Type TSO EXEC 'pds(rexx-member)' on the command line on any screen.

3.) Foreground again (most commonly used method)
• To run the rexx exec without giving specifying the path we can allocate the PDS containing the REXX member to SYSEXEC or SYSPROC -> these are the files that are searched to locate execs.
• Usually SYSPROC contains all user created CLISTS; so we can append our dataset to this (SYSPROC is like a dd-name to which datasets can be concatenated) -> use the ISRDDN command to check datasets allocated in your session.
• To allocate datasets to SYSPROC use the following TSO command: CONCAT FILE(SYSPROC) DA('pds name') SHR REUSE
• Now if your REXX code is called CREATOR then you can just say TSO CREATOR on any screen to invoke the tool. We can also say: %CREATOR
• One question is should I execute the CONCAT command each time I log into mainframes?
Yes - there are a couple of ways to circumvent this: take advantage of the initial logon screen where you can specify commands to be executed on logging in. Another option is that your mainframe might already have a specific dataset that is executed for each user on logging in; in this case just add the CONCAT step to this file.
4.) Running a REXX exec in batch

Write a jcl step to execute the tso program IKJEFT01.
In the SYSTSIN card we can give the TSO commands to be executed.
Ex:
//SYSTSIN DD * EX 'TEST.SS.REXX.EXEC(CREATOR)'
/*
If you want to pass arguments to your program, then use:
//SYSTSIN DD * %CREATOR arg1 arg2
/*
(CREATOR code should then have an ARG statement to capture the arguments).











http://www.tek-tips.com/viewthread.cfm?qid=1234003&page=9


tcurrier (Programmer)
23 May 06 10:55
In a Rexx exec, I am trying to concatenate 2 private libraries to ISPLLIB as follows:
CODE
ADDRESS TSO "ALLOC FI(MYLIB1) SHR DA('H2501S.T.DYNAMIC.LIBZ00')"
IF RC = 0 THEN CONCAT ISPLLIB MYLIB1
ADDRESS TSO "ALLOC FI(MYLIB2) SHR DA('H2501S.T.DYNAMIC.LIBZ01')"
IF RC = 0 THEN CONCAT ISPLLIB MYLIB2


I'm getting an error:

CONCAT- SPECIFIED DDNAME(S) ALREADY "OPEN"

Not sure if this belong in the 'Rexx' forum, but ... Thanks...

Tek-Tips Forums is Member Supported. Click Here to donate.
rexxhead (Programmer)
24 May 06 9:45
Not exactly REXX, but I don't believe there's an ISPF forum....

The error is because when ISPF starts, it OPENs all the ISPxLIB files. You can't concatenate to an already-open file. Not even CONCAT can do it.

Instead, you should LIBDEF:
CODE
address ISPEXEC "LIBDEF ISPPLIB DATASET ID('H2501S.T.DYNAMIC.LIBZ00') STACK"

This layers the datasets specified in "ID" above the current ISPLLIB. When you're finished using them,
CODE
address ISPEXEC "LIBDEF ISPPLIB"
and they evaporate.
Frank Clarke
Tampa Area REXX Programmers' Alliance
REXX Language Assn Listmaster

tcurrier (Programmer)
24 May 06 14:10
Thanks... using your method, I wasn't able to get it to work, though:

"ISPEXEC LIBDEF ISPLLIB DATASET ID('H2501S.T.DYNAMIC.LIBZ00') STACK"
"ISPEXEC LIBDEF ISPLLIB DATASET ID('H2501S.T.DYNAMIC.LIBZ01') STACK"

"ISPEXEC SELECT CMD(H2545Z00)"
IKJ56500I COMMAND H2545Z00 NOT FOUND

ADDRESS LINKPGM 'H2545Z00'
+++ RC(-3) +++

"ISPEXEC SELECT PGM(H2545Z00)"
Link to 'H2545Z00' failed, abend code = x'00000806'.
-------------------------------------------------------------
"ISPEXEC LIBDEF ISPLLIB DATASET
ID('H2501S.T.DYNAMIC.LIBZ00','H2501S.T.DYNAMIC.LIBZ01')"

"ISPEXEC SELECT PGM(H2545Z00)"
CEE3501S The module H2545Z01 was not found.
------------------------------------------------------------
"ISPEXEC LIBDEF ISPLLIB DATASET
ID('H2501S.T.DYNAMIC.LIBZ00','H2501S.T.DYNAMIC.LIBZ01')"

ADDRESS LINKPGM 'H2545Z00'
CEE3501S The module H2545Z01 was not found.
-------------------------------------------------------------
THIS IS THE ONLY WAY I COULD GET IT TO WORK :

"ISPEXEC LIBDEF ISPLLIB DATASET
ID('H2501S.T.DYNAMIC.LIBZ00','H2501S.T.DYNAMIC.LIBZ01')"

"ISPEXEC SELECT CMD(H2545Z00)"

*** NOW IN H2545Z00 ***
*** NOW IN H2545Z01 ***

--------------------------------------------------------------

kevinf2349 (TechnicalUser)
24 May 06 14:24
Thats because in the initial attempt you effectively caused the first LIBDEF to be thrown aside for the second one.

The way you eventually got it to work is the correct method.

rexxhead (Programmer)
25 May 06 10:50

He STACKed the LIBDEFs, so he actually has all of them available. Notice that he also switched to "SELECT CMD(" as Doug Nadel advised him to do over on MVSHELP. I think that was "the answer".
Frank Clarke
Tampa Area REXX Programmers' Alliance
REXX Language Assn Listmaster

kevinf2349 (TechnicalUser)
25 May 06 13:19
Whoops...my bad... I completely missed the STACK.




Example:

ALLOC FI(NOTCAT) DA('dsn.not.catalog') +
SHR REUSE UNIT(SYSALLDA) VOL(vvvvvv)
ALLOC FI(MYDD) DA('cat.dsn.#1' +
'cat.dsn.#2' +
'cat.dsn.#3') SHR REUSE
CONCAT (MYDD,NOTCAT)
OPENFILE MYDD INPUT
...




TSO ISRDDN

SYSEXEC


TSOCMD = ALLOC F(SYSPROC) DA('') SHR REUSE"
ADDRESS TSO TSOCMD