Problems inserting a row in a table with identity column

[roy]

Hi,
I would try defining a view on that table as work-around to try trick DS to think there is no additional column and perform the insert on the view.

Another option if could be concidered is to handle the identity column yourself by using a regular bigint column and supplying the serial value in the DS job rather then letting SQLServer do it.

Regarding the wierd characters, are they by any chance NLS related? (EE on windows has no NLS available yet)

IHTH,

[rshewale]

You should not have to include the identity column in the insert thorugh datastage, Try the insert directly using SQL statement and not specifying the identty column if it works first. If not what is the error.

[garirt]

What 'rshewale' says is right. Don't include that column in the stage at all. The SQL server will automatically update it.

[ray.wurlod]

I vaguely remember that providing zero for the identity column also triggers it to use an auto-incremented value. Or is that Red Brick?

Anyway, not inserting into the identity column at all will work, since "next available" is its default value.

[chulett]

I vaguely remember that providing zero for the identity column also triggers it to use an auto-incremented value. Or is that Red Brick?

Informix works the same way, from what I vaguely recall, when using its SERIAL field. We would load it with a zero to say "please give me the next available value". You could then query the SQLCA area (this gets even more vaguer) to find out what value it actually assigned if you needed that information downstream.

And before Ray busts me, yes... I know 'vaguer' is not a real word. Kind of hoping the more I use it, the more real it will get.

[ElBandido]

all: Thank you for your suggestions. Now I am waiting for DS licence renewal, then I will be able to try to apply them.

Roy: Using a view is an interesting work-around: I will try it. Handling myself the identity column has not worked in any way: Insert row failed is the nicest error I get. NLS is not enabled either in DS (of course) and SQL Server.

rshewale:

Try the insert directly using SQL statement

I wish I would be able to, but everytime I try, I get the error APT_CombinedOperatorController(0),0: Fatal Error: Fatal: SQL data type '%1' defined on column %2 is not supported. I guess user-defined SQL statements in DRS stages only work with Oracle by now. If my memory does not fail, something similar is also stated in DS documentation.

ray: It seems that providing zero for the identity column is interpreted by SQL Server like forcing the value. I get Insert row failed.

Craig: see above. In addition:
$> cd /pub
$> join Craig beer


Stefano

[T42]

Please paste your SQL statement. I have a sneaking suspicion about something.

[ElBandido]

As my licence has not been renewed yet, I cannot access at the code at the moment. So we both must trust my memory. However code has the following form:

Code: Select all

INSERT INTO ds.dbo.Sheet
  (CustCode, JobCode, SrcFileName, SrcPath, TrgFileName,
   TrgPath, PostDate, SysTs, AcqDate, CustID,
   PrnLnName, DivName, JobID, UsrID, SiteID,
   RecNumber, RealRecNum, CustDesc, OpID, Xfer)
values
  (?, ?, ?, ?, ?,
   ?, ?, ?, ?, ?,
   ?, ?, ?, ?, ?,
   ?, ?, ?, ?, ?)

Notes:
- Code is wrapped for readability purpose, but it is not wrapped in the stage;
- Link dependencies are in the right order and correctly computed;
- SheetID is the identity column, it is the first column in the table and it is not included in the column list of the statement, neither a corresponding value is.

Ready to know about your suspicion

[T42]

This is exactly what I thought happened. The most proper way to insert values within SQL from a Datastage stream is to use the following format:

Code: Select all

INSERT INTO [table] (field1, field2, field3...)
VALUES (ORCHESTRATE.INPUT_FIELD1, ORCHESTRATE.INPUT_FIELD2, ORCHESTRATE.INPUT_FIELD3...)

Basically, you can not use ? for input. You need to specifically state "orchestrate." then the specific input field. It does provide a nice flexibility of using a different name for input field to use to insert on any database field.

[ElBandido]

Many thanks for your suggestion, I will try as soon as possible.
The form you suggest sounds new to me in user-defined SQL statements, I only knew it as Ascential self-generated code. However, I did not expect it could work when used as user-defined statement, and I did not find it anywhere in the documents provided by Ascential.
The examples I have found for Oracle use the parameter placeholder form (:1, :2 etc.), so I tried to use the corresponding SQL Server form.
Stefano

[T42]

This is one of many things that are different between Server jobs and Parallel jobs.

Orchestrate is the actual engine's name running Parallel jobs. OSH code = Orchestrate Code.