DateTime Metadata Mismatch Warnings

GloriaRR · Post by **GloriaRR** » Wed Jan 24, 2007 3:21 pm

I work on a project that maintains a set of tables in SQL Server. We then make these tables available on other database platforms as needed. I am currently working on a project to put a copy of the tables on Informix.

Since the Informix tables have the same structure as the SQL Server tables, I wanted to reuse existing server jobs that extract data using an ODBC Stage from the SQL Server tables. I did this by setting the jobs to allow multiple instances, and used parameters to identify the data source, user ID, and password.

This worked fine except for the DateTime columns. Apparently SQL Server defines its DateTime with a length of 23 and a scale of 3, while Informix defines its DateTime with a length of 25 and a scale of 5. Thus the exact jobs run without warnings when the parameters point it to SQL Server. But when the parameters point it to Informix, it gets metadata mismatch warnings for all datetime fields.

Is there any way to suppress these metadata warnings or to somehow customize the column definitions in the ODBC stage so I can reuse the job without the warnings?

chulett · Post by **chulett** » Wed Jan 24, 2007 3:28 pm

Perhaps treat them as varchars? Meaning, is there some way generic enough so that handling them as strings would work in both databases?

GloriaRR · Post by **GloriaRR** » Wed Jan 24, 2007 4:16 pm

Your suggestion worked for the extract. In the ODBC stage I cast the datetime columns as varchar(26) in the Derivation field, changed the column length to 26, and removed the scale. When the job runs against either Informix or SQL Server it does not get the metadata error.

But I have the same problem on the other end of my set of jobs when I use an ODBC stage to write to either the SQL Server table or the Informix table (parameterized just like the extract step). This time I don't think something generic will work, since the database is expecting a datetime.

So I'm back to my original question, except this time it's for a load job instead of an extract job. Is there any way to suppress these metadata warnings or to somehow customize the column definitions in the ODBC stage so I can reuse the job without the warnings?

chulett · Post by **chulett** » Thu Jan 25, 2007 7:45 am

Sheesh, no other takers? :roll:

Ok, I'll try again.

What are the two formats expected by SQL Server and Informix? How different are they? Perhaps another generic attempt using varchar and a custom routine that formats the date appropriately? Pass in the date string and database type, pass back out what is needed.

Or does the SQL itself need to change for each database? Tell us what is needed and we'll see about getting you there.

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 8:11 am

I'd say go for Craig's solution. Make the date field as varchar. Pass in a parameter as DBType. Check for parameter value inside the job and in a stage variable switch betweent he two formats.

GloriaRR · Post by **GloriaRR** » Thu Jan 25, 2007 9:50 am

I'm not sure how to accomplish what either of you are recommending. So here's a little more detail of what exactly I'm doing.

The job consists of a sequential file stage containing the records to be loaded into the database and an ODBC stage that connects to the appropriate database based on a parameter. In the ODBC stage I have to provide the column names and types for the table being loaded. The table name, column names, and column types are the same in both databases.

The only difference is with the datetime columns.
- In SQL Server they are simply defined as "datetime". In the DataStage ODBC Stage, this becomes a column of type "Timestamp" with a length of 23 and a scale of 3. (Anything else gives the metadata mismatch error.)
- In Informix they are defined as "datetime year to fraction(3)". In the DataStage ODBC Stage, this becomes a column of type "Timestamp" with a length of 25 and a scale of 5. (Anything else gives the metadata mismatch error.) I tried changing the Informix definition to "datetime year to fraction(1)", but Informix length and scale did not change, and I lost two decimal places in the column's value.

When I execute the job to load to Informix with the ODBC definition that works for SQL Server, I receive the following warnings:

"DSD.BCIOpenR results of SQLColAttributes(creation_date) gave MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 3 Actual = 5" and

"DSD.BCIOpenR results of SQLColAttributes(creation_date) gave MetaData mismatch
COLUMN.PRECISION Expected = 23 Actual = 25"

In the ODBC stage I can't use a parameter for the column length and scale. If I could, I'd be all set. But I don't think I can just use varchar for the type, since that wouldn't match the database either and would give the same metadata mismatch error.

What am I missing?

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 9:53 am

Did you try executing with datatype as varchar and length say 26 and no scale on both the databases. You need to find what length of varchar datatype will work for both databases. If you can find that, you will be all set.

GloriaRR · Post by **GloriaRR** » Thu Jan 25, 2007 10:03 am

I've tried that, and there's still a warning.

I can change the type to varchar with a length of 26 and no scale. Then I get following warning:
"DSD.BCIOpenR results of SQLColAttributes(creation_date) gave MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 0 Actual = 5"

I can change the type to varchar with a length of 26 and a scale of 5, which works for Informix, but I get the following warning for SQL Server:
"DSD.BCIOpenR results of SQLColAttributes(creation_date) gave MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 5 Actual = 3"

So I really didn't accomplish anything.

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 10:25 am

A little bit off topic but tell me this, What does this date column represent? Is that the current timestamp ? Or a different datetime value needed from the source?

GloriaRR · Post by **GloriaRR** » Thu Jan 25, 2007 10:42 am

The creation_date was just one example. There are several datetime fields in the tables we maintain. There are creation dates, last change dates, active dates, inactive dates, etc. Sometimes the time part of it is not important, but other times it is important.

I also have the requirement that since the tables in Informix amount to a copy of the SQL Server tables, we need to keep the data in Informix exactly the same as it was in SQL Server. I could eliminate my frustration if I just created two separate jobs, and didn't try to reuse existing jobs, but I thought I'd give it a try.

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 10:53 am

GloriaRR wrote:I could eliminate my frustration if I just created two separate jobs, and didn't try to reuse existing jobs, but I thought I'd give it a try.

I think that could be your only way. I do not see how you can re-use the same job with different meta data for different targets. If the metadata was identical then you were all set, but unfortunately thats not the case. Maybe someone here can open other doors, untill then, start building your jobs.

GloriaRR · Post by **GloriaRR** » Thu Feb 01, 2007 9:38 am

After additional searching, I have found the problem to be with the DataDirect Informix driver that maps all Informix data types of "Datetime year to fraction(x)" as fraction (5), regardless of how the data is defined in Informix. In my Informix tables, I have "Datatime year to fraction(3)" since this matches what I have in SQL Server, but that obviously doesn't match the metadata in the Informix driver.

So, to make a long story short, I was able to reuse the extract job by casting the datetime columns as varchar. Unfortunately I'm not able to reuse the load job because there's no way to cast the column types for an insert/update statement, and changing the type to varchar still gives a metadata mismatch on the scale portion.

But at least I have the solution for the front half. Thanks for your help!

DSguru2B · Post by **DSguru2B** » Thu Feb 01, 2007 1:50 pm

Thats a good sign. Atleast you got half of it working. The data type mismatches are very critical to databases. I was hoping it would work as varchar but apparantly it did not.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 01, 2007 4:02 pm

For the load part, can you not simply append sufficient fractional seconds digits? Something like:

Code: Select all

Field(InLink.TheTime, ':", 1, 2) : ":" : Fmt(Field(InLink.TheTime, ":", 3, 1), "8R5##.#####")

(This form will work with both times and timestamps.)

Or, if you have only whole seconds:

Code: Select all

InLink.TheTime : ".00000"

Or, if you have exactly three fractional seconds digits:

Code: Select all

InLink.TheTime : "00"

Or find a better ODBC driver.

DSguru2B · Post by **DSguru2B** » Thu Feb 01, 2007 4:05 pm

The issue remains with the metadata, rather than data itself. Both hold different scales which is causing the problem.