zOs/TX/WK979DDL
set current sqlid = 'S100447';
$@[
if $phase <= 2 then $@=[
drop TABLESPACE $db.a979a;
commit;
$]
if $phase >= 1 & $phase <= 2 then $@=[
CREATE TABLESPACE a979a
IN $db
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
LOGGED
DSSIZE 32 G
NUMPARTS 8
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;
CREATE TABLE $creator.TWK979a1
(WK979PART_ID SMALLINT NOT NULL,
WK979REP_ORDER_ID CHAR(35) FOR SBCS DATA NOT NULL,
WK979PROC_STEP SMALLINT NOT NULL,
WK979STATUS INTEGER NOT NULL,
WK979POST_PROC SMALLINT NOT NULL WITH DEFAULT 0,
WK979PROC_TYP SMALLINT NOT NULL,
WK979PIR_DATA_SRC CHAR(1) FOR SBCS DATA NOT NULL,
WK979CIF_NO CHAR(12) FOR SBCS DATA NOT NULL,
WK979SAFEK_ACC_NO CHAR(16) FOR SBCS DATA NOT NULL,
WK979CLSTR_ID CHAR(21) FOR SBCS DATA NOT NULL,
WK979OWNER_CIF_NO CHAR(12) FOR SBCS DATA NOT NULL,
WK979PER_FR_DT DATE NOT NULL WITH DEFAULT '0001-01-01',
WK979PER_FR_TYP CHAR(1) FOR SBCS DATA NOT NULL,
WK979PER_TO_DT DATE NOT NULL WITH DEFAULT '9999-12-31',
WK979PER_TO_TYP CHAR(1) FOR SBCS DATA NOT NULL,
WK979SUBPER SMALLINT NOT NULL,
WK979PER_FR_DT_SI DATE NOT NULL WITH DEFAULT '0001-01-01',
WK979PER_TO_DT_SI DATE NOT NULL WITH DEFAULT '9999-12-31',
WK979SUBPER_SI SMALLINT NOT NULL,
WK979CALC_MTHD CHAR(1) FOR SBCS DATA NOT NULL,
WK979CALC_TYP CHAR(1) FOR SBCS DATA NOT NULL,
WK979FEES_DEDUCTED CHAR(1) FOR SBCS DATA NOT NULL,
WK979TAX_DEDUCTED CHAR(1) FOR SBCS DATA NOT NULL,
WK979PRINT_VERS CHAR(1) FOR SBCS DATA NOT NULL,
WK979PROC_MODE CHAR(1) FOR SBCS DATA NOT NULL,
WK979aUTO_DISPATCH CHAR(1) FOR SBCS DATA NOT NULL,
WK979PRINT_CENTRAL CHAR(1) FOR SBCS DATA NOT NULL,
WK979OUTP_LOC CHAR(8) FOR SBCS DATA NOT NULL,
WK979OUTP_LOC_BRCH CHAR(4) FOR SBCS DATA NOT NULL,
WK979PRINT_ADDR CHAR(12) FOR SBCS DATA NOT NULL,
WK979POV_LIST_NO CHAR(4) FOR SBCS DATA NOT NULL,
WK979SYS_ORIGORDER CHAR(8) FOR SBCS DATA NOT NULL,
WK979aDDR_INSTR CHAR(8) FOR SBCS DATA NOT NULL,
WK979aDDR_INSTR_AP CHAR(8) FOR SBCS DATA NOT NULL,
WK979LANGUAGE CHAR(1) FOR SBCS DATA NOT NULL,
WK979LANGUAGE_LE SMALLINT NOT NULL,
WK979RECIPIENT_PID CHAR(8) FOR SBCS DATA NOT NULL,
WK979SENDER_PID CHAR(8) FOR SBCS DATA NOT NULL,
WK979ORDERER_PID CHAR(8) FOR SBCS DATA NOT NULL,
WK979VLTN_CCY_ISO CHAR(3) FOR SBCS DATA NOT NULL,
WK979VLTN_CCY_CS CHAR(4) FOR SBCS DATA NOT NULL,
WK979REP_CCY_ISO CHAR(3) FOR SBCS DATA NOT NULL,
WK979REP_CCY_CS CHAR(4) FOR SBCS DATA NOT NULL,
WK979STA_IPC_DT DATE NOT NULL WITH DEFAULT '0001-01-01',
WK979PER_FR_DT_SIF CHAR(1) FOR SBCS DATA NOT NULL,
WK979IPC_CLEAN_DT DATE NOT NULL WITH DEFAULT '0001-01-01',
WK979RECV_PID CHAR(8) FOR SBCS DATA NOT NULL,
WK979RECV_BU CHAR(4) FOR SBCS DATA NOT NULL,
WK979RECV_BU_ABBR CHAR(4) FOR SBCS DATA NOT NULL,
WK979RECV_BRCH CHAR(4) FOR SBCS DATA NOT NULL,
WK979RECV_SLT CHAR(35) FOR SBCS DATA NOT NULL,
WK979RECV_NAME CHAR(35) FOR SBCS DATA NOT NULL,
WK979RECV_BU_DEPT CHAR(35) FOR SBCS DATA NOT NULL,
WK979RECV_STREET CHAR(35) FOR SBCS DATA NOT NULL,
WK979RECV_ZIP_LOC CHAR(35) FOR SBCS DATA NOT NULL,
WK979RECV_DEPT_CD CHAR(10) FOR SBCS DATA NOT NULL,
WK979RECV_STATUS CHAR(1) FOR SBCS DATA NOT NULL,
WK979OWNER_LE CHAR(4) FOR SBCS DATA NOT NULL,
WK979OWNER_BU CHAR(4) FOR SBCS DATA NOT NULL,
WK979OWNER_BU_ABBR CHAR(4) FOR SBCS DATA NOT NULL,
WK979OWNER_BRCH CHAR(4) FOR SBCS DATA NOT NULL,
WK979OWNER_DOMICIL CHAR(3) FOR SBCS DATA NOT NULL,
WK979OWNER_SKA_TYP CHAR(3) FOR SBCS DATA NOT NULL,
WK979OWNER_CUSTTYP CHAR(3) FOR SBCS DATA NOT NULL,
WK979OWNER_CLNT_CD CHAR(3) FOR SBCS DATA NOT NULL,
WK979OWNER_F_NAME CHAR(70) FOR SBCS DATA NOT NULL,
WK979OWNER_L_NAME CHAR(70) FOR SBCS DATA NOT NULL,
WK979OWNER_SADDR_1 CHAR(35) FOR SBCS DATA NOT NULL,
WK979OWNER_SADDR_2 CHAR(35) FOR SBCS DATA NOT NULL,
WK979OWNER_ADDR_1 CHAR(35) FOR SBCS DATA NOT NULL,
WK979OWNER_ADDR_2 CHAR(35) FOR SBCS DATA NOT NULL,
WK979OWNER_ADDR_3 CHAR(35) FOR SBCS DATA NOT NULL,
WK979OWNER_ADDR_4 CHAR(35) FOR SBCS DATA NOT NULL,
WK979OWNER_ADDR_5 CHAR(35) FOR SBCS DATA NOT NULL,
WK979OWNER_LANG SMALLINT NOT NULL,
WK979OWNER_EMPL_F CHAR(1) FOR SBCS DATA NOT NULL,
WK979OWNER_EAM_F CHAR(1) FOR SBCS DATA NOT NULL,
WK979OWNER_IADVPID CHAR(8) FOR SBCS DATA NOT NULL,
WK979OWNER_RMI_PID CHAR(8) FOR SBCS DATA NOT NULL,
WK979OWNER_OFFICE CHAR(7) FOR SBCS DATA NOT NULL,
WK979CD_WORD_ROC CHAR(15) FOR SBCS DATA NOT NULL,
WK979FREE_TEXT CHAR(100) FOR SBCS DATA NOT NULL,
WK979SCHED_FOR_DT DATE NOT NULL,
WK979SCHED_FOR_TYP CHAR(1) FOR SBCS DATA NOT NULL,
WK979TIME_DELAY SMALLINT NOT NULL,
WK979REP_RUN_TYP CHAR(1) FOR SBCS DATA NOT NULL,
WK979REP_PERY CHAR(1) FOR SBCS DATA NOT NULL,
WK979DURATION_END DATE NOT NULL,
WK979ORDER_VALID_T DATE NOT NULL WITH DEFAULT '0001-01-01',
WK979ORDER_PROC_DT DATE NOT NULL WITH DEFAULT '0001-01-01',
WK979ORDER_PROC_TI TIME NOT NULL WITH DEFAULT '00.00.00',
WK979P43_ORDER_KEY CHAR(59) FOR SBCS DATA NOT NULL,
WK979NO_OF_COPIES INTEGER NOT NULL,
WK979OUTP_L_CSDMSI INTEGER NOT NULL,
WK979PVS_BRCH_NAME CHAR(28) FOR SBCS DATA NOT NULL,
WK979RM_PID CHAR(8) FOR SBCS DATA NOT NULL,
WK979RM_SEX_CD CHAR(1) FOR SBCS DATA NOT NULL,
WK979RM_TITLE CHAR(33) FOR SBCS DATA NOT NULL,
WK979RM_NAME CHAR(33) FOR SBCS DATA NOT NULL,
WK979RM_PHONE CHAR(33) FOR SBCS DATA NOT NULL,
WK979RM_ZIP_BRCH CHAR(33) FOR SBCS DATA NOT NULL,
WK979TRX_PER_FR_DT DATE NOT NULL WITH DEFAULT '0001-01-01',
WK979TRX_PER_TO_DT DATE NOT NULL WITH DEFAULT '0001-01-01',
WK979MANDATE_CLNT CHAR(1) FOR SBCS DATA NOT NULL,
WK979PRT_MAND_INFO CHAR(1) FOR SBCS DATA NOT NULL,
WK979UPDT_TS TIMESTAMP NOT NULL WITH DEFAULT,
WK979UPDT_PID_PGM CHAR(8) FOR SBCS DATA NOT NULL,
CONSTRAINT CWK979PK
PRIMARY KEY (WK979REP_ORDER_ID),
CONSTRAINT CWK979aA CHECK (WK979PROC_STEP >= 0 ),
CONSTRAINT CWK979aB CHECK (WK979PROC_TYP >= 0 ),
CONSTRAINT CWK979aC
CHECK (WK979PIR_DATA_SRC IN ( 'A' , 'I' ) ),
CONSTRAINT CWK979aD CHECK (WK979SUBPER >= 0 ),
CONSTRAINT CWK979aE CHECK (WK979SUBPER_SI >= 0 ),
CONSTRAINT CWK979aF
CHECK (WK979FEES_DEDUCTED IN ( 'Y' , 'N' ) ),
CONSTRAINT CWK979aG
CHECK (WK979TAX_DEDUCTED IN ( 'Y' , 'N' ) ),
CONSTRAINT CWK979aH
CHECK (WK979aUTO_DISPATCH IN ( 'Y' , 'N' ) ),
CONSTRAINT CWK979aJ
CHECK (WK979RECV_STATUS IN ( 'I' , 'A' ) ),
CONSTRAINT CWK979aK CHECK (WK979OWNER_LANG >= 1 ),
CONSTRAINT CWK979aL
CHECK (WK979PER_FR_DT_SIF IN ( 'Y' , 'N' ) ),
CONSTRAINT CWK979aN
CHECK (WK979OWNER_EMPL_F IN ( 'Y' , 'N' ) ),
CONSTRAINT CWK979aO
CHECK (WK979OWNER_EAM_F IN ( 'Y' , 'N' ) ),
CONSTRAINT CWK979aP CHECK (WK979TIME_DELAY >= 0 ),
CONSTRAINT CWK979aR CHECK (WK979OUTP_L_CSDMSI >= 0 ),
CONSTRAINT CWK979aS CHECK (WK979NO_OF_COPIES >= 0 ),
CONSTRAINT CWK979aT
CHECK (WK979PRINT_CENTRAL IN ( 'Y' , 'N' ) ),
CONSTRAINT CWK979LA CHECK (WK979LANGUAGE_LE >= 1 ),
CONSTRAINT CWK979PI CHECK (WK979PART_ID > 0 ),
CONSTRAINT CWK979PP CHECK (WK979POST_PROC >= 0 ),
CONSTRAINT CWK979RO CHECK (WK979REP_ORDER_ID <> '' ),
CONSTRAINT CWK979SF
CHECK (WK979SCHED_FOR_TYP IN ( 'I' , 'O' , 'P' , ' ' ) ),
CONSTRAINT CWK979ST
CHECK (WK979STATUS IN ( 0 , 4 , 8 , 12 ) ),
CONSTRAINT CWK979UP CHECK (WK979UPDT_PID_PGM <> '' ))
IN $db.a979a
PARTITION BY (WK979PART_ID ASC)
(PART 1 VALUES(1),
PART 2 VALUES(2),
PART 3 VALUES(3),
PART 4 VALUES(4),
PART 5 VALUES(5),
PART 6 VALUES(6),
PART 7 VALUES(7),
PART 8 VALUES(8))
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
--
LABEL ON TABLE $creator.TWK979a1 IS 'ReportOrder ';
--
COMMENT ON $creator.TWK979a1
(WK979UPDT_PID_PGM IS 'UpdatePIDPgm',
WK979UPDT_TS IS 'UpdateTimestamp ',
WK979PRT_MAND_INFO IS 'FlagPrintMandateInfo ',
WK979MANDATE_CLNT IS 'FlagMandateClient ',
WK979TRX_PER_TO_DT IS 'PeriodToDateTransactionList ',
WK979TRX_PER_FR_DT IS 'PeriodFromDateTransactionList ',
WK979RM_ZIP_BRCH IS 'RelationshipZIPBranch ',
WK979RM_PHONE IS 'RelationshipManagerPhone ',
WK979RM_NAME IS 'RelationshipManagerName ',
WK979RM_TITLE IS 'RelationshipManagerTitle ',
WK979RM_SEX_CD IS 'RelationshipManagerSexCode ',
WK979RM_PID IS 'RelationshipManagerPID ',
WK979PVS_BRCH_NAME IS 'PVSBranchName ',
WK979OUTP_L_CSDMSI IS 'OutputLinesCSDMSI ',
WK979NO_OF_COPIES IS 'NumberOfCopies ',
WK979P43_ORDER_KEY IS 'P43Orderkey ',
WK979ORDER_PROC_TI IS 'OrderProcessingTime ',
WK979ORDER_PROC_DT IS 'OrderProcessingDate ',
WK979ORDER_VALID_T IS 'OrderValidTo ',
WK979DURATION_END IS 'DurationEnd ',
WK979REP_PERY IS 'ReportPeriodicity ',
WK979REP_RUN_TYP IS 'ReportRunType ',
WK979TIME_DELAY IS 'TimeDelayForGeneratingReport ',
WK979SCHED_FOR_TYP IS 'ScheduledForType ',
WK979SCHED_FOR_DT IS 'ScheduledForDate ',
WK979FREE_TEXT IS 'FreeText ',
WK979CD_WORD_ROC IS 'ROCCodeWord ',
WK979OWNER_OFFICE IS 'OwnerCustomerOffice ',
WK979OWNER_RMI_PID IS 'OwnerRelationshipManagerPID ',
WK979OWNER_IADVPID IS 'OwnerInvestmentAdvisorPID ',
WK979OWNER_EAM_F IS 'OwnerEAMFlag ',
WK979OWNER_EMPL_F IS 'OwnerEmployeeFlag ',
WK979OWNER_LANG IS 'OwnerLanguage ',
WK979OWNER_ADDR_5 IS 'OwnerAddress5 ',
WK979OWNER_ADDR_4 IS 'OwnerAddress4 ',
WK979OWNER_ADDR_3 IS 'OwnerAddress3 ',
WK979OWNER_ADDR_2 IS 'OwnerAddress2 ',
WK979OWNER_ADDR_1 IS 'OwnerAddress1 ',
WK979OWNER_SADDR_2 IS 'OwnerShortAddress2 ',
WK979OWNER_SADDR_1 IS 'OwnerShortAddress1 ',
WK979OWNER_L_NAME IS 'OwnerLastName ',
WK979OWNER_F_NAME IS 'OwnerFirstName ',
WK979OWNER_CLNT_CD IS 'OwnerClientCode ',
WK979OWNER_CUSTTYP IS 'OwnerCustomerType ',
WK979OWNER_SKA_TYP IS 'OwnerSafekeepingAccountType ',
WK979OWNER_DOMICIL IS 'OwnerDomicile ',
WK979OWNER_BRCH IS 'OwnerBranch ',
WK979OWNER_BU_ABBR IS 'OwnerBUAbbreviation ',
WK979OWNER_BU IS 'OwnerBU ',
WK979OWNER_LE IS 'OwnerLegalEntity ',
WK979RECV_STATUS IS 'ReceiverStatus ',
WK979RECV_DEPT_CD IS 'ReceiverDepartmentCode ',
WK979RECV_ZIP_LOC IS 'ReceiverZIPLocation ',
WK979RECV_STREET IS 'ReceiverStreet ',
WK979RECV_BU_DEPT IS 'ReceiverBUandDepartmentCode ',
WK979RECV_NAME IS 'ReceiverName ',
WK979RECV_SLT IS 'ReceiverSalutation ',
WK979RECV_BRCH IS 'ReceiverBranch ',
WK979RECV_BU_ABBR IS 'ReceiverBUAbbreviation ',
WK979RECV_BU IS 'ReceiverBU ',
WK979RECV_PID IS 'ReceiverPID ',
WK979IPC_CLEAN_DT IS 'LastIPCDataCleanupDate ',
WK979PER_FR_DT_SIF IS 'FlagPerFromDateSinceInception ',
WK979STA_IPC_DT IS 'IPCStartDate(Inception) ',
WK979REP_CCY_CS IS 'ReportCurrencyCS ',
WK979REP_CCY_ISO IS 'ReportCurrencyISO ',
WK979VLTN_CCY_CS IS 'ValuationCurrencyCS ',
WK979VLTN_CCY_ISO IS 'ValuationCurrencyISO ',
WK979ORDERER_PID IS 'OrdererPID ',
WK979SENDER_PID IS 'SenderPID ',
WK979RECIPIENT_PID IS 'RecipientPID ',
WK979LANGUAGE_LE IS 'LanguageForReportLegalEntity ',
WK979LANGUAGE IS 'LanguageOfReportOrder ',
WK979aDDR_INSTR_AP IS 'AddressInstructionApplied ',
WK979aDDR_INSTR IS 'AddressInstruction ',
WK979SYS_ORIGORDER IS 'SystemOrderOriginating ',
WK979POV_LIST_NO IS 'POVListNumber ',
WK979PRINT_ADDR IS 'PrintAddress ',
WK979OUTP_LOC_BRCH IS 'OutputLocationBranch ',
WK979OUTP_LOC IS 'OutputLocation ',
WK979PRINT_CENTRAL IS 'PrintCentral ',
WK979aUTO_DISPATCH IS 'AutomaticDispatch ',
WK979PROC_MODE IS 'ProcessingMode ',
WK979PRINT_VERS IS 'PrintVersion ',
WK979TAX_DEDUCTED IS 'TaxDeducted ',
WK979FEES_DEDUCTED IS 'FeesDeducted ',
WK979CALC_TYP IS 'CalculationType ',
WK979CALC_MTHD IS 'CalculationMethod ',
WK979SUBPER_SI IS 'SubPeriodSI ',
WK979PER_TO_DT_SI IS 'PeriodToDateSI ',
WK979PER_FR_DT_SI IS 'PeriodFromDateSI ',
WK979SUBPER IS 'SubPeriod ',
WK979PER_TO_TYP IS 'PeriodToType ',
WK979PER_TO_DT IS 'PeriodToDate ',
WK979PER_FR_TYP IS 'PeriodFromType ',
WK979PER_FR_DT IS 'PeriodFromDate ',
WK979OWNER_CIF_NO IS 'OwnerCifNo ',
WK979CLSTR_ID IS 'ClusterId ',
WK979SAFEK_ACC_NO IS 'SafekeepingAccountNo ',
WK979CIF_NO IS 'CifNo ',
WK979PIR_DATA_SRC IS 'PIRDataSource ',
WK979PROC_TYP IS 'ProcessingType ',
WK979POST_PROC IS 'StatusPostProcessing ',
WK979STATUS IS 'Status ',
WK979PROC_STEP IS 'ProcessingStep ',
WK979REP_ORDER_ID IS 'RepOrderId ',
WK979PART_ID IS 'PartitionID ');
CREATE UNIQUE INDEX $creator.IWK979a0
ON $creator.TWK979a1
(WK979REP_ORDER_ID ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
;
$*(
SET CURRENT SCHEMA='$creator';
CREATE UNIQUE INDEX IWK979a1
ON $creator.TWK979a1
(WK979P43_ORDER_KEY ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
CREATE INDEX $creator.IWK979a2
ON $creator.TWK979a1
(WK979PART_ID ASC,
WK979REP_ORDER_ID ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
CREATE INDEX IWK979a3
ON $creator.TWK979a1
(WK979CLSTR_ID ASC,
WK979SCHED_FOR_TYP ASC,
WK979PIR_DATA_SRC ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","$creator" ;
--
CREATE VIEW $creator.VWK979a1 AS
SELECT ALL $creator.TWK979a1.WK979PART_ID,
$creator.TWK979a1.WK979REP_ORDER_ID,
$creator.TWK979a1.WK979PROC_STEP,
$creator.TWK979a1.WK979STATUS,
$creator.TWK979a1.WK979POST_PROC,
$creator.TWK979a1.WK979PROC_TYP,
$creator.TWK979a1.WK979PIR_DATA_SRC,
$creator.TWK979a1.WK979CIF_NO,
$creator.TWK979a1.WK979SAFEK_ACC_NO,
$creator.TWK979a1.WK979CLSTR_ID,
$creator.TWK979a1.WK979OWNER_CIF_NO,
$creator.TWK979a1.WK979PER_FR_DT,
$creator.TWK979a1.WK979PER_FR_TYP,
$creator.TWK979a1.WK979PER_TO_DT,
$creator.TWK979a1.WK979PER_TO_TYP,
$creator.TWK979a1.WK979SUBPER,
$creator.TWK979a1.WK979PER_FR_DT_SI,
$creator.TWK979a1.WK979PER_TO_DT_SI,
$creator.TWK979a1.WK979SUBPER_SI,
$creator.TWK979a1.WK979CALC_MTHD,
$creator.TWK979a1.WK979CALC_TYP,
$creator.TWK979a1.WK979FEES_DEDUCTED,
$creator.TWK979a1.WK979TAX_DEDUCTED,
$creator.TWK979a1.WK979PRINT_VERS,
$creator.TWK979a1.WK979PROC_MODE,
$creator.TWK979a1.WK979aUTO_DISPATCH,
$creator.TWK979a1.WK979PRINT_CENTRAL,
$creator.TWK979a1.WK979OUTP_LOC,
$creator.TWK979a1.WK979OUTP_LOC_BRCH,
$creator.TWK979a1.WK979PRINT_ADDR,
$creator.TWK979a1.WK979POV_LIST_NO,
$creator.TWK979a1.WK979SYS_ORIGORDER,
$creator.TWK979a1.WK979aDDR_INSTR,
$creator.TWK979a1.WK979aDDR_INSTR_AP,
$creator.TWK979a1.WK979LANGUAGE,
$creator.TWK979a1.WK979LANGUAGE_LE,
$creator.TWK979a1.WK979RECIPIENT_PID,
$creator.TWK979a1.WK979SENDER_PID,
$creator.TWK979a1.WK979ORDERER_PID,
$creator.TWK979a1.WK979VLTN_CCY_ISO,
$creator.TWK979a1.WK979VLTN_CCY_CS,
$creator.TWK979a1.WK979REP_CCY_ISO,
$creator.TWK979a1.WK979REP_CCY_CS,
$creator.TWK979a1.WK979STA_IPC_DT,
$creator.TWK979a1.WK979PER_FR_DT_SIF,
$creator.TWK979a1.WK979IPC_CLEAN_DT,
$creator.TWK979a1.WK979RECV_PID, $creator.TWK979a1.WK979RECV_BU,
$creator.TWK979a1.WK979RECV_BU_ABBR,
$creator.TWK979a1.WK979RECV_BRCH,
$creator.TWK979a1.WK979RECV_SLT,
$creator.TWK979a1.WK979RECV_NAME,
$creator.TWK979a1.WK979RECV_BU_DEPT,
$creator.TWK979a1.WK979RECV_STREET,
$creator.TWK979a1.WK979RECV_ZIP_LOC,
$creator.TWK979a1.WK979RECV_DEPT_CD,
$creator.TWK979a1.WK979RECV_STATUS,
$creator.TWK979a1.WK979OWNER_LE,
$creator.TWK979a1.WK979OWNER_BU,
$creator.TWK979a1.WK979OWNER_BU_ABBR,
$creator.TWK979a1.WK979OWNER_BRCH,
$creator.TWK979a1.WK979OWNER_DOMICIL,
$creator.TWK979a1.WK979OWNER_SKA_TYP,
$creator.TWK979a1.WK979OWNER_CUSTTYP,
$creator.TWK979a1.WK979OWNER_CLNT_CD,
$creator.TWK979a1.WK979OWNER_F_NAME,
$creator.TWK979a1.WK979OWNER_L_NAME,
$creator.TWK979a1.WK979OWNER_SADDR_1,
$creator.TWK979a1.WK979OWNER_SADDR_2,
$creator.TWK979a1.WK979OWNER_ADDR_1,
$creator.TWK979a1.WK979OWNER_ADDR_2,
$creator.TWK979a1.WK979OWNER_ADDR_3,
$creator.TWK979a1.WK979OWNER_ADDR_4,
$creator.TWK979a1.WK979OWNER_ADDR_5,
$creator.TWK979a1.WK979OWNER_LANG,
$creator.TWK979a1.WK979OWNER_EMPL_F,
$creator.TWK979a1.WK979OWNER_EAM_F,
$creator.TWK979a1.WK979OWNER_IADVPID,
$creator.TWK979a1.WK979OWNER_RMI_PID,
$creator.TWK979a1.WK979OWNER_OFFICE,
$creator.TWK979a1.WK979CD_WORD_ROC,
$creator.TWK979a1.WK979FREE_TEXT,
$creator.TWK979a1.WK979SCHED_FOR_DT,
$creator.TWK979a1.WK979SCHED_FOR_TYP,
$creator.TWK979a1.WK979TIME_DELAY,
$creator.TWK979a1.WK979REP_RUN_TYP,
$creator.TWK979a1.WK979REP_PERY,
$creator.TWK979a1.WK979DURATION_END,
$creator.TWK979a1.WK979ORDER_VALID_T,
$creator.TWK979a1.WK979ORDER_PROC_DT,
$creator.TWK979a1.WK979ORDER_PROC_TI,
$creator.TWK979a1.WK979P43_ORDER_KEY,
$creator.TWK979a1.WK979NO_OF_COPIES,
$creator.TWK979a1.WK979OUTP_L_CSDMSI,
$creator.TWK979a1.WK979PVS_BRCH_NAME,
$creator.TWK979a1.WK979RM_PID, $creator.TWK979a1.WK979RM_SEX_CD,
$creator.TWK979a1.WK979RM_TITLE, $creator.TWK979a1.WK979RM_NAME,
$creator.TWK979a1.WK979RM_PHONE,
$creator.TWK979a1.WK979RM_ZIP_BRCH,
$creator.TWK979a1.WK979TRX_PER_FR_DT,
$creator.TWK979a1.WK979TRX_PER_TO_DT,
$creator.TWK979a1.WK979MANDATE_CLNT,
$creator.TWK979a1.WK979PRT_MAND_INFO,
$creator.TWK979a1.WK979UPDT_TS,
$creator.TWK979a1.WK979UPDT_PID_PGM
froOM $creator.TWK979a1 ;
LABEL ON TABLE $creator.VWK979a1 IS 'ReportOrder ';
$*)
commit;
$]
if $phase = 2 then $@=[
ALTER TABLE $creator.Twk979A1 alter wk979NO_OF_COPIES
set DEFAULT 0;
ALTER TABLE $creator.Twk979A1 alter wk979PVS_BRCH_NAME
set DEFAULT ' ';
ALTER TABLE $creator.Twk979A1 alter wk979RM_SEX_CD
set DEFAULT ' ';
ALTER TABLE $creator.Twk979A1 add
wk979PROC_PRIO INTEGER NOT NULL default -999;
ALTER TABLE $creator.Twk979A1 DROP PRIMARY KEY ;
DROP INDEX $creator.Iwk979A0;
-- DROP INDEX $creator.Iwk979A1;
-- DROP INDEX $creator.Iwk979A2;
-- DROP INDEX $creator.Iwk979A3;
commit;
$@do px=9 to $parts $@=[
ALTER TABLE $creator.Twk979A1 ADD PARTITION ENDING AT ($px);
$]
CREATE UNIQUE INDEX $creator.Iwk979A0
ON $creator.Twk979A1
(wk979PART_ID ASC,
wk979REP_ORDER_ID ASC)
PARTITIONED
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER YES
DEFINE YES;
ALTER TABLE $creator.Twk979A1 ADD CONSTRAINT Cwk979PK
PRIMARY KEY (wk979PART_ID,
wk979REP_ORDER_ID);
commit;
$]
if $phase = 3 then $@=[
ALTER TABLE $creator.Twk979A1 alter
wk979PROC_PRIO drop default;
commit;
$]
$]
$*(
ALTER TABLE $creator.Twk979A1 DROP PRIMARY KEY ;
SET CURRENT SCHEMA='$creator';
CREATE UNIQUE INDEX Iwk979A1
ON $creator.Twk979A1
(wk979PART_ID ASC,
wk979P43_ORDER_KEY ASC)
PARTITIONED
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER YES
DEFINE YES;
CREATE INDEX $creator.Iwk979A2
ON $creator.Twk979A1
(wk979PART_ID ASC,
wk979REP_ORDER_ID ASC)
PARTITIONED
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER YES
DEFINE YES;
CREATE INDEX Iwk979A3
ON $creator.Twk979A1
(wk979PART_ID ASC,
wk979CLSTR_ID ASC,
wk979SCHED_FOR_TYP ASC,
wk979PIR_DATA_SRC ASC)
PARTITIONED
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER YES
DEFINE YES;
CREATE INDEX Iwk979A4
ON $creator.Twk979A1
(wk979PART_ID ASC,
wk979PROC_STEP ASC,
wk979STATUS ASC,
wk979POST_PROC ASC)
PARTITIONED
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER YES
DEFINE YES;
CREATE INDEX Iwk979A5
ON $creator.Twk979A1
(wk979PART_ID ASC,
wk979PROC_PRIO ASC)
PARTITIONED
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 10 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER YES
DEFINE YES;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","$creator" ;
SET CURRENT SCHEMA = '$creator' ;
CREATE VIEW $creator.Vwk979A1 AS
SELECT ALL wk979PART_ID, wk979REP_ORDER_ID, wk979PROC_STEP,
wk979STATUS, wk979POST_PROC, wk979PROC_TYP,
wk979PIR_DATA_SRC, wk979CIF_NO, wk979SAFEK_ACC_NO,
wk979CLSTR_ID, wk979OWNER_CIF_NO, wk979PER_FR_DT,
wk979PER_FR_TYP, wk979PER_TO_DT, wk979PER_TO_TYP,
wk979SUBPER, wk979PER_FR_DT_SI, wk979PER_TO_DT_SI,
wk979SUBPER_SI, wk979CALC_MTHD, wk979CALC_TYP,
wk979FEES_DEDUCTED, wk979TAX_DEDUCTED, wk979PRINT_VER
wk979PROC_MODE, wk979AUTO_DISPATCH, wk979PRINT_CENTRA
wk979OUTP_LOC, wk979OUTP_LOC_BRCH, wk979PRINT_ADDR,
wk979POV_LIST_NO, wk979SYS_ORIGORDER, wk979ADDR_INSTR
wk979ADDR_INSTR_AP, wk979LANGUAGE, wk979LANGUAGE_LE,
wk979RECIPIENT_PID, wk979SENDER_PID, wk979ORDERER_PID
wk979VLTN_CCY_ISO, wk979VLTN_CCY_CS, wk979REP_CCY_ISO
wk979REP_CCY_CS, wk979STA_IPC_DT, wk979PER_FR_DT_SIF,
wk979IPC_CLEAN_DT, wk979RECV_PID, wk979RECV_BU,
wk979RECV_BU_ABBR, wk979RECV_BRCH, wk979RECV_SLT,
wk979RECV_NAME, wk979RECV_BU_DEPT, wk979RECV_STREET,
wk979RECV_ZIP_LOC, wk979RECV_DEPT_CD, wk979RECV_STATU
wk979OWNER_LE, wk979OWNER_BU, wk979OWNER_BU_ABBR,
wk979OWNER_BRCH, wk979OWNER_DOMICIL, wk979OWNER_SKA_T
wk979OWNER_CUSTTYP, wk979OWNER_CLNT_CD, wk979OWNER_F_
wk979OWNER_L_NAME, wk979OWNER_SADDR_1, wk979OWNER_SAD
wk979OWNER_ADDR_1, wk979OWNER_ADDR_2, wk979OWNER_ADDR
wk979OWNER_ADDR_4, wk979OWNER_ADDR_5, wk979OWNER_LANG
wk979OWNER_EMPL_F, wk979OWNER_EAM_F, wk979OWNER_IADVP
wk979OWNER_RMI_PID, wk979OWNER_OFFICE, wk979CD_WORD_R
wk979FREE_TEXT, wk979SCHED_FOR_DT, wk979SCHED_FOR_TYP
wk979TIME_DELAY, wk979REP_RUN_TYP, wk979REP_PERY,
wk979DURATION_END, wk979ORDER_VALID_T, wk979ORDER_PRO
wk979ORDER_PROC_TI, wk979P43_ORDER_KEY, wk979NO_OF_CO
wk979OUTP_L_CSDMSI, wk979PVS_BRCH_NAME, wk979RM_PID,
wk979RM_SEX_CD, wk979RM_TITLE, wk979RM_NAME, wk979RM_
wk979RM_ZIP_BRCH, wk979TRX_PER_FR_DT, wk979TRX_PER_TO
wk979MANDATE_CLNT, wk979PRT_MAND_INFO, wk979UPDT_TS,
wk979UPDT_PID_PGM, wk979PROC_PRIO
FROM $creator.Twk979A1 ;
LABEL ON TABLE $creator.Vwk979A1 IS 'ReportOrder '
ROLLBACK;;;;
rollback; ;;;
$*)