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;
Wednesday, November 4, 2009
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
/* 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('
ADDRESS TSO TSOCMD
Wednesday, July 2, 2008
Platinum commands
S COMMAND COMMAND DESCRIPTION
RC/QUERY TABLE REPORT OPTIONS
_ A - RQTA Table Alias
_ D - RQTD Table Detail
_ L - RQTL Table List
_ P - RQTP Table Plan Inquiry
_ S - RQTS Table Synonym Inquiry
_ I - RQTI Table Index Inquiry
_ C - RQTC Table Column Inquiry
_ V - RQTV Table View Inquiry
_ HL - RQTHL Table History List
_ HD - RQTHD Table Detail History
_ HS - RQTHS Table Stats. History
_ HP - RQTHP Table Partition History
_ O - RQOD Object Dependency
_ UA - RQTUA Table User Authorization Inq
_ PA - RQTPA Table Plan Authorization Inq
_ KA - RQTKA Table Package Authorization Inq
_ PK - RQTPK Table Package Inquiry
_ UC - RQTUCS Table Unique Constraint Inquiry
_ DI - RQTDI Table Drop Impact
_ LR - RQTLR Table Lob Relationships
_ TG - RQTTG Table Trigger
RC/QUERY AVAILABLE REPORT OBJECTS
_ C OPTIONS - Column
_ SY OPTIONS - System
_ RI OPTIONS - Referential Integrity
_ U OPTIONS - User
GENERAL LINE COMMANDS
_ COUNT - Display number of table rows
_ DDL - Create DDL for an object
_ DELETE - Jump to ISQL to delete from a table or view
S COMMAND COMMAND DESCRIPTION
_ DISPLAY - Jump to DB2C to display a DB/TS/IS/UDF or Stored Proc.
_ DISPLKS - Display database/tablespace/indexspace with LOCKS
_ DISPLPL - Display database/tablespace/indexspace with LPL
_ DISPUSE - Display database/tablespace/indexspace with USE
_ FSTART - Start a database and/or tablespace/indexspace
_ FSTOP - Stop a database and/or tablespace/indexspace
_ INSERT - Jump to ISQL to insert into a table or view
_ LGRANT - Grant privileges to users
_ LOAD - Create JCL for LOAD Utility
_ LREVOKE - Revoke privileges from users
_ REFRESH - Refresh Materialized Query Table
_ SELECT - Jump to ISQL to select from a table or view
_ START - Jump to DB2C to start a DB/TS/IS/UDF or Stored Proc.
_ STARO - Jump to DB2C to start a DB/TS/IS in READ ONLY status
_ STAUT - Jump to DB2C to start a DB/TS/IS in UTILITY status
_ STAFO - Jump to DB2C to start a DB/TS/IS with ACCESS FORCE
_ STOP - Jump to DB2C to stop a DB/TS/IS/UDF or Stored Proc.
_ UPDATE - Jump to ISQL to update a table or view
_ UNLOAD - Create JCL for the FAST UNLOAD utility
LICENSED LINE COMMANDS
_ ALTER - Jump to RC/Update to alter an object
_ BROWSE - Jump to RC/Update to browse a table
_ COPYD - Jump to RC/Update to copy a table
_ COPYAUTH - Jump to RC/Secure to copy authorizations
_ CREATE - Jump to RC/Update to create an object
_ DROP - Jump to RC/Update to drop an object
_ EDIT - Jump to RC/Update to edit a table
_ FBROWSE - Jump to RC/Update to browse a table
_ FEDIT - Jump to RC/Update to edit a table
_ GRANT - Jump to RC/Secure for GRANT services
_ QMIGRATE - Jump to RC/Migrator to perform Quick Migrations
_ REVOKE - Jump to RC/Secure for REVOKE services
_ TEMPLATE - Jump to RC/Update to template an object
_ LISTCAT - IDCAMS Listcat of tablespace/indexspace
_ EX - Execute Clist or REXX Exec
_ TESTEX1 - SAMPLE REXX EXEC
******************************* BOTTOM OF DATA ************
S COMMAND COMMAND DESCRIPTION
RC/QUERY SYNONYM REPORT OPTIONS
_ L - RQSL Synonym List
_ T - RQST Synonym Table Inquiry
_ V - RQSV Synonym View Inquiry
_ P - RQSP Synonym Plan Inquiry
_ PK - RQSPK Synonym Package Inquiry
_ DI - RQSDI Synonym Drop Impact
RC/QUERY AVAILABLE REPORT OBJECTS
_ T OPTIONS - Table
_ C OPTIONS - Column
_ SY OPTIONS - System
_ RI OPTIONS - Referential Integrity
_ U OPTIONS - User
RC/QUERY TABLE REPORT OPTIONS
_ A - RQTA Table Alias
_ D - RQTD Table Detail
_ L - RQTL Table List
_ P - RQTP Table Plan Inquiry
_ S - RQTS Table Synonym Inquiry
_ I - RQTI Table Index Inquiry
_ C - RQTC Table Column Inquiry
_ V - RQTV Table View Inquiry
_ HL - RQTHL Table History List
_ HD - RQTHD Table Detail History
_ HS - RQTHS Table Stats. History
_ HP - RQTHP Table Partition History
_ O - RQOD Object Dependency
_ UA - RQTUA Table User Authorization Inq
_ PA - RQTPA Table Plan Authorization Inq
_ KA - RQTKA Table Package Authorization Inq
_ PK - RQTPK Table Package Inquiry
_ UC - RQTUCS Table Unique Constraint Inquiry
_ DI - RQTDI Table Drop Impact
_ LR - RQTLR Table Lob Relationships
_ TG - RQTTG Table Trigger
RC/QUERY AVAILABLE REPORT OBJECTS
_ C OPTIONS - Column
_ SY OPTIONS - System
_ RI OPTIONS - Referential Integrity
_ U OPTIONS - User
GENERAL LINE COMMANDS
_ COUNT - Display number of table rows
_ DDL - Create DDL for an object
_ DELETE - Jump to ISQL to delete from a table or view
S COMMAND COMMAND DESCRIPTION
_ DISPLAY - Jump to DB2C to display a DB/TS/IS/UDF or Stored Proc.
_ DISPLKS - Display database/tablespace/indexspace with LOCKS
_ DISPLPL - Display database/tablespace/indexspace with LPL
_ DISPUSE - Display database/tablespace/indexspace with USE
_ FSTART - Start a database and/or tablespace/indexspace
_ FSTOP - Stop a database and/or tablespace/indexspace
_ INSERT - Jump to ISQL to insert into a table or view
_ LGRANT - Grant privileges to users
_ LOAD - Create JCL for LOAD Utility
_ LREVOKE - Revoke privileges from users
_ REFRESH - Refresh Materialized Query Table
_ SELECT - Jump to ISQL to select from a table or view
_ START - Jump to DB2C to start a DB/TS/IS/UDF or Stored Proc.
_ STARO - Jump to DB2C to start a DB/TS/IS in READ ONLY status
_ STAUT - Jump to DB2C to start a DB/TS/IS in UTILITY status
_ STAFO - Jump to DB2C to start a DB/TS/IS with ACCESS FORCE
_ STOP - Jump to DB2C to stop a DB/TS/IS/UDF or Stored Proc.
_ UPDATE - Jump to ISQL to update a table or view
_ UNLOAD - Create JCL for the FAST UNLOAD utility
LICENSED LINE COMMANDS
_ ALTER - Jump to RC/Update to alter an object
_ BROWSE - Jump to RC/Update to browse a table
_ COPYD - Jump to RC/Update to copy a table
_ COPYAUTH - Jump to RC/Secure to copy authorizations
_ CREATE - Jump to RC/Update to create an object
_ DROP - Jump to RC/Update to drop an object
_ EDIT - Jump to RC/Update to edit a table
_ FBROWSE - Jump to RC/Update to browse a table
_ FEDIT - Jump to RC/Update to edit a table
_ GRANT - Jump to RC/Secure for GRANT services
_ QMIGRATE - Jump to RC/Migrator to perform Quick Migrations
_ REVOKE - Jump to RC/Secure for REVOKE services
_ TEMPLATE - Jump to RC/Update to template an object
_ LISTCAT - IDCAMS Listcat of tablespace/indexspace
_ EX - Execute Clist or REXX Exec
_ TESTEX1 - SAMPLE REXX EXEC
******************************* BOTTOM OF DATA ************
S COMMAND COMMAND DESCRIPTION
RC/QUERY SYNONYM REPORT OPTIONS
_ L - RQSL Synonym List
_ T - RQST Synonym Table Inquiry
_ V - RQSV Synonym View Inquiry
_ P - RQSP Synonym Plan Inquiry
_ PK - RQSPK Synonym Package Inquiry
_ DI - RQSDI Synonym Drop Impact
RC/QUERY AVAILABLE REPORT OBJECTS
_ T OPTIONS - Table
_ C OPTIONS - Column
_ SY OPTIONS - System
_ RI OPTIONS - Referential Integrity
_ U OPTIONS - User
picture string
= any character . invalid characters
@ alphabetic characters - non-numeric characters
# numeric characters < lower case alphabetics
$ special characters > upper case alphabetics
¬ non-blank characters
@ alphabetic characters - non-numeric characters
# numeric characters < lower case alphabetics
$ special characters > upper case alphabetics
¬ non-blank characters
FTP DOS
verboseopen 159.xxx.xxx.xxuseridpwwdxxascd "'PFM.SBA.LDDNB504.D200608'";quo site xlate=canftptrquo site xlate=canftpnzquo site autorecall pr
lcd C:\SBA-AUG06
get AUDREF LDDNB504.txt
cd "'PFM.SBA.LDDNB7A.D200608'"get AUDREF LDDNB7A.txt
cd "'PFM.SBA.LDSBA504.D200608'" get AUDREF LDSBA504.txt
cd "'PFM.SBA.LDSBA7A.D200608'" get AUDREF LDSBA7A.txt
cd "'PFM.SBA.LNDNB504.D200608'" get AUDREF LNDNB504.txt
cd "'PFM.SBA.LNDNB7A.D200608'" get AUDREF LNDNB7A.txt
bye
lcd C:\SBA-AUG06
get AUDREF LDDNB504.txt
cd "'PFM.SBA.LDDNB7A.D200608'"get AUDREF LDDNB7A.txt
cd "'PFM.SBA.LDSBA504.D200608'" get AUDREF LDSBA504.txt
cd "'PFM.SBA.LDSBA7A.D200608'" get AUDREF LDSBA7A.txt
cd "'PFM.SBA.LNDNB504.D200608'" get AUDREF LNDNB504.txt
cd "'PFM.SBA.LNDNB7A.D200608'" get AUDREF LNDNB7A.txt
bye
rowid
Our env: DB2 v8.1 on AIX5.3
My question is, I have a SQL statement which works fine in oracle...
update mytab set c1 = -c1 where rowid = v_rowid;
I need an equivalent in db2 (I am aware rowid equivalent is row_number() over() but I am wondering how to assign the left hand part of rowid because mytab table does not contain the column rowid!!).
Any ideas? thanks.
dr_suresh20 View Public Profile Send a private message to dr_suresh20 Send email to dr_suresh20 Find all posts by dr_suresh20
#2 08-07-06, 11:44 Peter.Vanroose Registered User Join Date: Sep 2004Location: BelgiumPosts: 602 Essentially there is no equivalent in DB2.The logic behind this being that there is no "natural" rowid attached to a certain row: a certain row can be the 4th result row from a certain query, but the same row could as well be the 6th row from the SAME query on the SAME table when the optimizer chose to implement the query differently! (E.g., with or without the use of an index.)
Typically you probably wanted to change a value in the row with rowid=4 because some (independent) program logic found out that this row had a certain property, i.e., "where" condition.In that case you could do one of three things:1. Add that condition to your UPDATE statement:update mytab set c1 = -c1 where CONDITION;2. Suppose you are running through a result table with a cursor, and at a certain iteration the condition applies for the current row; in that case useupdate mytab set c1 = -c1 where CURRENT OF cursorname;3. Use a subquery to produce the primary key values of mytab that satisfy the condition:update mytab set c1 = -c1 where mytab.pkey IN ( SELECT pkey FROM mytab WHERE CONDITION );__________________--_Peter Vanroose,__IBM Certified Database Administrator, DB2 V8 z/OS__IBM Certified Application Developer, DB2 V8__ABIS Training and Consulting__http://www.abis.be/
Every record stored has a ROWID; which is a combination of page(block)#,slot #(within a page) and file#(and optionally object#). ROWID is the fastest way of getting to a record; is used by indexes. Oracle provides it as a pseudocolumn; db2 does NOT provide rowid. Rowid is fixed,constant, immutable till you do a REORG. If you analyze some records and you want to modify or select those records again, you should store their rowids so that you can get to them most efficiently. ROWNUM is different; IN db2 rowid is 4 bytes; 3 for page# and one for slot#; one byte, 8 bits can go up to 255; that's why you have 255 record limit per page; in VIPER you have 4byte page and 2 byte slot#;so you have bigger limits on table size and # of records per page.
My question is, I have a SQL statement which works fine in oracle...
update mytab set c1 = -c1 where rowid = v_rowid;
I need an equivalent in db2 (I am aware rowid equivalent is row_number() over() but I am wondering how to assign the left hand part of rowid because mytab table does not contain the column rowid!!).
Any ideas? thanks.
dr_suresh20 View Public Profile Send a private message to dr_suresh20 Send email to dr_suresh20 Find all posts by dr_suresh20
#2 08-07-06, 11:44 Peter.Vanroose Registered User Join Date: Sep 2004Location: BelgiumPosts: 602 Essentially there is no equivalent in DB2.The logic behind this being that there is no "natural" rowid attached to a certain row: a certain row can be the 4th result row from a certain query, but the same row could as well be the 6th row from the SAME query on the SAME table when the optimizer chose to implement the query differently! (E.g., with or without the use of an index.)
Typically you probably wanted to change a value in the row with rowid=4 because some (independent) program logic found out that this row had a certain property, i.e., "where" condition.In that case you could do one of three things:1. Add that condition to your UPDATE statement:update mytab set c1 = -c1 where CONDITION;2. Suppose you are running through a result table with a cursor, and at a certain iteration the condition applies for the current row; in that case useupdate mytab set c1 = -c1 where CURRENT OF cursorname;3. Use a subquery to produce the primary key values of mytab that satisfy the condition:update mytab set c1 = -c1 where mytab.pkey IN ( SELECT pkey FROM mytab WHERE CONDITION );__________________--_Peter Vanroose,__IBM Certified Database Administrator, DB2 V8 z/OS__IBM Certified Application Developer, DB2 V8__ABIS Training and Consulting__http://www.abis.be/
Every record stored has a ROWID; which is a combination of page(block)#,slot #(within a page) and file#(and optionally object#). ROWID is the fastest way of getting to a record; is used by indexes. Oracle provides it as a pseudocolumn; db2 does NOT provide rowid. Rowid is fixed,constant, immutable till you do a REORG. If you analyze some records and you want to modify or select those records again, you should store their rowids so that you can get to them most efficiently. ROWNUM is different; IN db2 rowid is 4 bytes; 3 for page# and one for slot#; one byte, 8 bits can go up to 255; that's why you have 255 record limit per page; in VIPER you have 4byte page and 2 byte slot#;so you have bigger limits on table size and # of records per page.
qmf commands
LIST QUERIES
LIST TABLES
Command What it does
CONVERT Converts a prompted, QBE, or SQL query into an equivalent SQL
query. The comments in the original query do not appear in the
converted query.
DISPLAY Retrieve an object from the database and display it on your
terminal.
EDIT Edit a table in the database using the Table Editor. From the
database object list, you can only use the EDIT command to edit
a table. If you want to edit a query or procedure, you must
display it first.
ERASE Delete an object from the database.
EXIT End your QMF session.
EXPORT Export QMF objects stored in the database directly from the
database into a file (CMS), data set (TSO and CICS/MVS), or
queue name (CICS).
IMPORT Import QMF objects directly into the database from a file
(CMS), data set (TSO and CICS/MVS), or data queue name (CICS).
LAYOUT Display the format of a report produced from a given form
without using any data. You can only use LAYOUT with form
objects, and only in an environment in which both REXX and ISPF
are available.
PRINT Print a database object.
RUN Execute a query or procedure stored in the database.
SAVE Replace the object in the database with the object currently in
temporary storage. For example, if you enter:
SAVE QUERY AS
next to a query on the database object list, QMF replaces that
query in the database with the query currently in temporary
storage.
Using a Placeholder on the List of Database Objects
You can use a slash / as a placeholder to represent the object type, owner,
and name in a QMF command.
For example, entering the following in the Action area for a table object:
EDIT / (MODE=ADD
means the same as entering:
EDIT TABLE owner.tablename (MODE=ADD
where owner.tablename is the owner and name of the table listed.
You can also use /T if you just want to specify the object type, or /N if
you just want to specify the owner and name. The /T and /N placeholders
are especially useful if you are issuing a command to run a user-written
application that requires just the object type or just the object owner an
name.
If you are displaying a list from a remote location, the placeholder
symbols (/ and /N) include the location with the owner and name.
You can also display the prompt panel for a command with the object type
and the object owner and name filled in. To do this, type the command
followed by the / placeholder and a question mark.
For example, to display the RUN Command Prompt panel for the DEPTQUERY
object, enter "RUN / ?" in the Action area next to the object. The RUN
QUERY Command Prompt panels display. The first panel already has the
The placeholder options that you can enter are:
- "/T" - to display object type
- "/N" - to display owner.name
- "/" - to display both object type and owner.name
LIST TABLES
Command What it does
CONVERT Converts a prompted, QBE, or SQL query into an equivalent SQL
query. The comments in the original query do not appear in the
converted query.
DISPLAY Retrieve an object from the database and display it on your
terminal.
EDIT Edit a table in the database using the Table Editor. From the
database object list, you can only use the EDIT command to edit
a table. If you want to edit a query or procedure, you must
display it first.
ERASE Delete an object from the database.
EXIT End your QMF session.
EXPORT Export QMF objects stored in the database directly from the
database into a file (CMS), data set (TSO and CICS/MVS), or
queue name (CICS).
IMPORT Import QMF objects directly into the database from a file
(CMS), data set (TSO and CICS/MVS), or data queue name (CICS).
LAYOUT Display the format of a report produced from a given form
without using any data. You can only use LAYOUT with form
objects, and only in an environment in which both REXX and ISPF
are available.
PRINT Print a database object.
RUN Execute a query or procedure stored in the database.
SAVE Replace the object in the database with the object currently in
temporary storage. For example, if you enter:
SAVE QUERY AS
next to a query on the database object list, QMF replaces that
query in the database with the query currently in temporary
storage.
Using a Placeholder on the List of Database Objects
You can use a slash / as a placeholder to represent the object type, owner,
and name in a QMF command.
For example, entering the following in the Action area for a table object:
EDIT / (MODE=ADD
means the same as entering:
EDIT TABLE owner.tablename (MODE=ADD
where owner.tablename is the owner and name of the table listed.
You can also use /T if you just want to specify the object type, or /N if
you just want to specify the owner and name. The /T and /N placeholders
are especially useful if you are issuing a command to run a user-written
application that requires just the object type or just the object owner an
name.
If you are displaying a list from a remote location, the placeholder
symbols (/ and /N) include the location with the owner and name.
You can also display the prompt panel for a command with the object type
and the object owner and name filled in. To do this, type the command
followed by the / placeholder and a question mark.
For example, to display the RUN Command Prompt panel for the DEPTQUERY
object, enter "RUN / ?" in the Action area next to the object. The RUN
QUERY Command Prompt panels display. The first panel already has the
The placeholder options that you can enter are:
- "/T" - to display object type
- "/N" - to display owner.name
- "/" - to display both object type and owner.name
Subscribe to:
Posts (Atom)