Working of Parameter Markers

[gradkarthik]

Hi
I have some doubts about the working of parameter markers. in one of my jobs i have a col with values like 0.69999999 which i want to round off to 0.7 and output to a sql server table.
I do this in the transformer by specifying the datatype for that col on output side as decimal (2,1).
the value gets rounded off to 0.7 which is seen in the log message on director but the sql returns an error msg as

Code: Select all

[DataStage][SQL Client]Parameter marker text size exceeds allocated space

while doing the insert.
I searched on the forum and found a post which says that if there n cols then there would be n parameter markers but doesnt explain how the parameter markers work.
my doubt is if DS is already rounding off the value in transformer stage, then SQL server should not give the error unless the rounding off is being done by the SQL database.
If anyone can point where i am going wrong..
Karthik

[ArndW]

Greetings to Arizona, gradkarthik (my home).

DataStage server doesn't perform an actual data conversion by just specifying the new column width in a transform, you need to do an explicit conversion on this column to convert it. I am not at a DS system right now, but I would use the FMT(In.Column,'R2') {please check the format function, I am not sure that this is a correct format}

[kcbland]

OCONV(link.column, "MD20") will shift the decimal 0 places and round to 2 decimals. MD24 means shift 4 places and round to 2. MD00 means no shift and round up to no decimals.

[gradkarthik]

Hi to all,
thanks ArndW and KcBland for the suggestions. I was playing around with the job when i observed that if i ran the same job with just specifying decimal(2,1) on the output link and output the result to a flat file then without using oconv or fmt in the transformer, the value gets rounded off.

Its only when you write to a relational database table like Sql server when the parameter marker comes into the picture. I tried using the oconv and it worked fine.

But the doubt still remains of why when writing to flat file, DS rounds off values by just specifying resultant metadata while when writing to sql server, it explicitly asks for oconv.

Thanks for your time.
Karthik

[ray.wurlod]

Is this ODBC?

By default the maximum row size you can transfer via ODBC is 8192 bytes. You can change this for a particular DSN or globally by placing MAXFETCHBUFF directives in uvodbc.config file.

Similarly the default maximum number of columns is 400, and the configuration parameter is MAXFETCHCOLS.

[gradkarthik]

Hi Ray,
I didnt understand what the MAXFETCHBUFF and maximum number of columns (MAXFETCHCOLS) have to do with the rounding of data in a particular column.

My main question now is how DS rounds off data by just specifying say, Decimal(2,1) on the output side in transformer stage for a particular col, when you are writing to a flat file.
whereas when you run the same job with only difference of writing to a sql server table via ODBC stage, gives the
[DataStage][SQL Client]Parameter marker text size exceeds allocated space error.

Karthik

[ray.wurlod]

I addressed a possible cause of Parameter marker text size exceeds allocated space