Date Update Process
$30-35 USD
Paid on delivery
Project overview: The DB2 accepts dates prior to 1800, but SQL does not.
We're moving the data from DB2 to SQL and need to automate an update process in DB2 before the SQL task grabs the data & moves it to from DB2 to SQL.
(1) Create a table based on the predifined SELECT statments
? See the? attached file.
Something like that:
create TABLE UPD ( KEY CHAR(50) , BAD_DATE DATE , GOOD_DATE DATE)
INSERT INTO TABLE UPD (select KEY, BAD_DATE,
,case when right ( cast ( year(RECEIVED_DT) as char(4)),2) < '10' then '20' || ltrim( rtrim ( right ( cast ( year(RECEIVED_DT) as char(4)),2) ))
|| '/' || ltrim( rtrim ( cast ( month(RECEIVED_DT) as char(2)) ))
|| '/' || ltrim( rtrim ( cast ( day(RECEIVED_DT) as char(2)) ))
else '19' || right ( cast ( year(RECEIVED_DT) as char(4)),2)
|| '/' || ltrim( rtrim ( cast ( month(RECEIVED_DT) as char(2)) ))
|| '/' || ltrim( rtrim (cast ( day(RECEIVED_DT) as char(2)) ))
end GOOD_DATE from DB2_TABLE where yeaR (RECEIVED_DT) <1800 )
Please note: all these tables are huge, the select I am using is not really fast and could be optimized.
(2) Set-up a process which will read the data from the table(#1).
This table will hold the KEY, BAD_DATE, GOOD_DATE, TABLE_NAME columns.
The update process will update the record in DB2 tables based on the table name, date and the key every Saturday night at 11:00pm.
Thanks!
## Deliverables
Platform: AIX
DB: DB2 UDB
Project ID: #3104188