Server Job ; User defined SQL

[jones]

Hi to all,

In my server job, I have to execute, set of SQL statements (Insert, update), I am using user defined SQL. I am getting error "This SQL statement did not contain the same number of parameters as columns on this link".
And for insert and update, I am using subquery results in the query not the input column values, (like "Insert into A (select C.Col1 Col from B as C)).

I can't use Stored Procedures, because, our DB2 server won't support because of Complier license issues.

Any ideas, how to solve this. Thks, Jones

[ray.wurlod]

The mechanism by which DataStage transfers values from itself to your SQL is parameter markers. These are either "?" or of the form ":1", ":2" and so on, depending on the database.
Look at the generated SQL, perhaps in a different job. It will look something like

Code: Select all

INSERT INTO Owner.Table(col1,col2,col3) VALUES (?,?,?); 

or

Code: Select all

INSERT INTO Owner.Table(col1,col2,col3) VALUES (:1,:2,:3); 

or

Code: Select all

UPDATE Owner.Table SET col2 = ?, col3 = ? WHERE col1 = ?;

or

Code: Select all

UPDATE Owner.Table SET col2 = :2, col3 = :3 WHERE col1 = :1;

Mimic these!

[chulett]

You are going to have to split your inserts from your updates if you are using a different number of columns in the different sqls. Even when using custom sql you must have parameter markers for all of the columns in the stage. Your error message is telling you this as well.

Use two separate links and you should be able to do want you want, I would think.

[kduke]

I think you want to do a before sql statement. This is just a pass through query. Ray is trying to tell you this is not how you build a job stream in DataStage. You select your data then transform it and then output it. You cannot change the insert or update that much on the output. It has to have the columns to fill in like :1,:2 and so on. The number of :1 or ? to fill in is a based on the number of columns in your metadata. It says they do not match because you removed them all.

I think most pass through queries should be done in the client front end like sqlplus for Oracle. Create a shell script that runs your sql. Like:

sqlplus user@sid MySqlScript.sql

Shell out and run this. I do not know DB2 but substitute your command for sqlplus. Surely DB2 has the equivalent command.

[ray.wurlod]

DB2's equivalent command is the eponymous db2. :D