IBM Data Lifecycle - Integration and Governance Connect with experts and peers to elevate technical expertise, solve problems and share insights. Join / Log in
I am running a simple convert request and generating data in CSV format. Then I am using the generated CSV file to load into Big Query Database.
while generating CSV file on Optim, Optim generates date format as M/DD/YYYY and timestamp as M/DD/YYYY HH:MM:SS.000000 AM
where as BQ needs data as YYYY-MM-DD and timestamp as YYYY-MM-DDThh:mm:ss.
is there any easy way on Optim to change the format of date instead of convert it to string and modify the format ?
help will be much appreciated .
The format that Optim uses for Date colum is driven by the date format in your source database. Either you can modify the date format in your source DB or you can try following manipulation via Optim to reformat the date/timestamp column. I have not implemented this concept before, so it may or may not work :)
Suppose you have a table "A" with 2 columns
Step1: create a view "A_view"
Step2: In your table map for convert, use "A_view" as destination table
Step3: Create a column map procedure to perform dummy aging and change date format. Apply the procedure on "Some_char" column and inside read/write to "Some_date" column
pro=age, mtd=rep, month=-12, year=1, srcdf="%MM/%DD/%YYYY %HH:%MI:%SS:%FFFFFF",dstdf="%YYYY-%MM-%DD %HH:%MI:%SS", flddef1=(name="Some_char", dt=DATETIME_WSZ)
I believe %YYYY-%MM-%DD %HH:%MI:%SS format will work for inserts in BigQuery.
Another option is to CONVERT the date column in your Extract SQL. Not sure what the source data DBMS is, but most of them have ways to CONVERT date and time fields.
A way to manipulate Optim to allow this is via a UNION ALL on the same table whereby the top part of the Union generates zero rows and the lower part is where you can manipulate the table columns and apply your WHERE conditions. We use the process outlined below to manipulate or null out columns from tables in an Access Definition. The format in the example below would be for SQL Server, but conceptually should work with other source DBMS.
Hope this helps.
SELECT * FROM SOURCE_TABLE WHERE 1=0
SELECT A.COLUMN_1,A.COLUMN_2,,CONVERT (A.DATE_COLUMN, VARCHAR, GETDATE(), #) [# is the date format you're converting to; there are charts online listing options],A.COLUMN4, etc.FROM SOURCE_TABLE AS AWHERE whatever criteria you specify
Thanks Ankur. I will try it out.
Hey Keith Tidball,
I agree with SQL part. I can replicate the same Union SQL command for my DB2 table which might solve my issue.
But where do we provide the this Union all SQL statement? Where in Optim AD?
Thanks Every one for your response. i am able resolve the issue by changing the date format in the data source.