InfoSphere Optim

 View Only
  • 1.  IBM Optim Convert request change date column format

    Posted Thu February 09, 2023 07:38 PM

    HI ALl,

    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 . 



    ------------------------------
    rajesh guttikonda
    ------------------------------


  • 2.  RE: IBM Optim Convert request change date column format

    Posted Fri February 10, 2023 04:20 AM

    Hi Rajesh,

    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

    • Some_char  defined as VARCHAR/STRING
    • Some_date defined as DateTimestamp

    Step1: create a view "A_view"

    • Some_char  defined as VARCHAR/STRING
    • Some_date defined as VARCHAR/STRING

    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.



    ------------------------------
    Thanks,
    Ankur Agrawal
    ------------------------------



  • 3.  RE: IBM Optim Convert request change date column format

    Posted Mon February 13, 2023 01:20 PM

    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. 

    -Keith 

    Example SQL:

    SELECT * 
    FROM SOURCE_TABLE 
    WHERE 1=0

    UNION ALL

    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 A
    WHERE whatever criteria you specify



    ------------------------------
    Keith Tidball
    Progressive Insurance
    ------------------------------



  • 4.  RE: IBM Optim Convert request change date column format

    Posted Mon February 13, 2023 04:42 PM

    Thanks Ankur. I will try it out.



    ------------------------------
    rajesh guttikonda
    ------------------------------



  • 5.  RE: IBM Optim Convert request change date column format

    Posted Mon February 13, 2023 04:45 PM

    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?



    ------------------------------
    rajesh guttikonda
    ------------------------------



  • 6.  RE: IBM Optim Convert request change date column format
    Best Answer

    Posted Sun February 19, 2023 09:47 PM

    Thanks Every one for your response. i am able resolve the issue by changing the date format in the data source. 



    ------------------------------
    rajesh guttikonda
    ------------------------------