How to convert sybase timestamp to Oracl timestamp

[jherr22]

I have input Seq File stage, pointing to a csv file which came from Sybase, with a Timestamp field whose data looks like:
Feb 10 2007 10:30:30:450AM
1) In the columns view (of the Seq File Stage) should the length of the timestamp be specified?
2) What type of conversion (function, macro, or whatever) should be used within the Transform Stage to generate an Oracle-compatible value as input to the ODBC Oracle Stage?

Thanks afrorehand,
-- john

[DSguru2B]

1)Yes.
2)Look into the timestamp functions inside the transformer or in the px developers guide. THere is a complete list. I believe you need to change it to string and then to timestamp again, but not to sure.

[jherr22]

There appears to be 3 different timestamp formats, viz:
a) Sybase,
b) Ascential, and
c) Oracle.

I guess my real problem is that I don't understand how we determine if the data from the input file is accepted as a valid timestamp format (in Sybase or Ascential format???), prior to exercising any derivation function.

Somehow I have to use AT LEAST 2 functions to go thru all 3 formats.

And then you suggest that I use a String as an intermediate step, which
now create 5 steps:
a) Sybase
b) string
c) Ascential
d) string, and
e) Oracle
Now I am really confused.

Are there any examples available is see how this is done?
-- john

[us1aslam1us]

Have you checked or tried the SDK function DategenericToODBCTimestamp and other functions. Also what exactly you mean by Ascential format?

[DSguru2B]

Feb 10 2007 10:30:30:450AM

Do you have a space before AM?

[jherr22]

There is no space before AM; should there be?

I'll look into DategenericToODBCTimestep()
Thanks
-- john

[DSguru2B]

DategenericToODBCTimestep() is a server function and not present in PX. You need to write a parsing C program to bring your input into a oracle format and then apply StringToTimestamp() function on it.

[DSguru2B]

Use the following C function to convert your input into YYYY-MM-DD 24HH:mm:ss.nnn format. THen use StringToTimestamp() to convert the string to timestamp.

Code: Select all

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

char* SybaseToOracleTp(char* InTp)
{
  //Initialize variables
  const int SIZE = 30;
  char* month = (char *)malloc(SIZE);
  char* day = (char *)malloc(SIZE);
  char* year = (char *)malloc(SIZE);
  char* hour = (char *)malloc(SIZE);
  char* newHr = (char *)malloc(SIZE);
  char* min = (char *)malloc(SIZE);
  char* sec = (char *)malloc(SIZE);
  char* msec = (char *)malloc(SIZE);
  char* time = (char *)malloc(SIZE);
  char* intMon = (char *)malloc(SIZE);
  char* finOut = (char *)malloc(SIZE);
  const char* calender[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};

  int hr = 0;

  //AM or PM
  char* p = strstr(InTp, "PM");

  //Disect the Date
  strcpy(month, strtok(InTp, " "));
  strcpy(day, strtok(NULL, " "));
  strcpy(year, strtok(NULL, " "));
  strcpy(time, strtok(NULL," "));

 //Disect Time
  strcpy(hour, strtok(time, ":"));
  strcpy(min, strtok(NULL, ":"));
  strcpy(sec, strtok(NULL, ":"));
  strcpy(msec, strtok(NULL, ":"));

  //get numeric representation of Month
  for(int i = 0; i < 12; i++)
  {
   if (strcmp(month, calender[i]) == 0)
     sprintf(intMon, "%02d", i + 1);
  }
  if ((p) && strcmp(hour, "12") != 0)
    {
     hr = atoi(hour);
     hr+=12;
     sprintf(hour, "%02d", hr);
    }
 
  if ((!p) && strcmp(hour, "12") == 0)
  {
	  strcpy(hour, "00");
  }
 //format string to YYYY-MM-DD HH:MM:SS.sss
 sprintf(finOut, "%s-%2s-%s %s:%s:%s.%s", year, intMon, day, hour, min, sec, msec);

 //free memory
 free(month);
 free(day);
 free(year);
 free(hour);
 free(min);
 free(sec);
 free(msec);
 free(time);
 free(intMon);

 return finOut;

}

[us1aslam1us]

Woow!! But does it accept any timestamp Format as Input?

[DSguru2B]

No Sam, this is just for the input shown by the OP. Because i am disecting the date and I know that the first part is going to be Month in three character string, the second part is Day and so on. It wont work for any input format.

[ray.wurlod]

... but you could adapt it to your own requirements.

[DSguru2B]

Most certainly. Maybe a generic routine could be created to handle all date and timestamp formats.

[ray.wurlod]

Including all foreign language representations? If you claim to write anything "generic" in DataStage you will need to make use of NLS locales, for example 23 AVR 2007 is a valid date in Canada, France and lots of other places.

[DSguru2B]

Including all foreign language representations?

Possibly. Nothing is impossible. Just needs time. Maybe some kind hearted soul will do that for us

[jherr22]

I am not getting anywhere with this Timestamp problem.
This post will simplify the problem, in hopes of advice.

Within the Seq. File Stage, I now point to a CSV file, each row with 6 fields, with each field in double quotes, and each separated by a coma
(and no trailing coma).
The 4th field in the row is a Timestamp.

I loaded the table definition with the 6 fields, the 4th of which is a
Timestamp, with length 26, and no scale value, and not nullable.
The data in the 4th field is: "Jan 10 2007 10:30:31:246AM" which is
26 chars long. (The data came from a Timestamp in Sybase.)

I then bring up Seq. File Properties, selecting the columns tab, and then I click on the "View Data", and I get:
"Bad trailing quote character at field "my_date"; looking for '34' but found '0', at offset: 89"

This makes no sense to me.
I have tried everything:
-- changing the length of the column definition,
-- dropping the double quotes between the comas,
and many other attempts.
Nothing will bring up the timestamp (using View Data)

If I switch the field type from Timestamp to Varchar, "my_date" shows up just fine (under View Data).

Within DataStage Manager | selected Project | Properties button | Parallel tab , and then under "Timestamp String" I see the default Timestamp string which is:
%yyyy-%mm-%dd %hh:%nn:%ss
I have no idea when this default format is used, but I decided to try it.

So, I tried changing my data in the input file to look like that, and I still
could not get View Data to bring up the data.

In a previous post by Brian, C-code is provided, which is great, which I am now trying to compile into a library.
But, if View Data cannot see the data, (which means to me that the data will be rejected upon Job Execution), then how could a C-function operate on it?

I'm baffled.
Any possibility of help with this?
Thanks
-- john