Ignoring a column in the target DB stage

[VCInDSX]

Hi,
I am having some trouble getting this job to work.... Would appreciate your help and input.

Here is my job design.
SequentialFile ==> Transformer ==> SQLServerEnterprise

The job reads an input text file with 3 columns and loads them into a target database (SQL Server 2005).

Input file has 3 columns (EmpID, EmpName, Address1).
[EmpId] [varchar](50)
[EmpName] [nvarchar](255)
[Address1] [nvarchar](255)

Target DB has 4 columns (EmpID, EmpName, Address1, RecCreatedDate).
The RecCreatedDate column is a DateTime type and has a default value population of current timestamp in its DDL and need not be loaded by any client application.

When I compile and run this job, I am getting errors as given below
MSDB_TargetDB: Table has a column(s) not present in input dataset [sqlsrvrWriteRep.C:112]
MSDB_TargetDB: Input schema and DB schema do not match [sqlsrvrWrite.C:613]
main_program: Could not check all operators because of previous error(s) [api\step_rep.C:1174]

I also see the following 3 warnings
MSDB_TargetDB: When checking operator: The view adapter has a binding for the non-existent destination field "EmpId". [api\interface_rep.C:6812]
MSDB_TargetDB: When checking operator: The view adapter has a binding for the non-existent destination field "EmpName". [api\interface_rep.C:6812]
MSDB_TargetDB: When checking operator: The view adapter has a binding for the non-existent destination field "Address1". [api\interface_rep.C:6812]

Is this not possible in a parallel job or am I missing some setting/parameter that will allow this?

I designed the same logic in a Server job using this same file and target DB and it worked without any glitches.

I would be glad to provide any additional information that will be useful in helping me in resolving this issue.

Thanks in advance for your time and help,

[VCInDSX]

Hi,
Just to make sure my earlier post was not confusing, I would like to summarize it as follows.

I have a database table with 4 columns and the input feed has only 3 columns. A job should read the input file with 3 columns and load data into the 3 columns and not worry about the 4th column.

1. I am able to do this in a server job
2. Can this be done in a parallel job - Without RCP?

Many thanks in advance for your time and help,

[rajeshkadiri]

r u enter new column in transformer stage?

(3columns)input------->transformer stage(4 columns)--------->
targer(4columns)

[VCInDSX]

In the server job, i don't have the 4th column in my transformer. Just mapped the 3 input columns to the output columns. The 4th column is not used in the table definition for the target stage also.
The Server version works without any glitches. The back-end database populates the 4th column using the system date.

As for the parallel job, I tried both approaches
a) Adding a derivation for the 4th column in the transformer
b) Ignoring the 4th column in the transformer (Back-end DB should handle it)
Both versions are not working. RCP is not enabled here.

Let me know if any additional details about this task will be helpful.

[ray.wurlod]

HOW does "the back-end database populates the 4th column using the system date"? Is this a trigger or a default value?

[VCInDSX]

Ray,
Thanks for the followup and your time.

The back-end database (SQL Server 2005) assigns a default value to this 4th column

The following is the excerpt from the DDL that creates this column.
RawRecCreatedDate [datetime] NULL CONSTRAINT [Dft_Employee_RawRecCreatedDate] DEFAULT (getdate())

Thanks again,

[ray.wurlod]

OK now post the SQL generated by your target DB stage. That is, the INSERT statement.

[VCInDSX]

Hi Ray,
I presume you wanted to review the INSERT from my Server Job as that is the working version in this discussion, so far.
INSERT INTO #RawLoaderJobParams.DB_TABLE_NAME#("EmpId", "EmpName", "Address1") VALUES (?,?,?);

If this is not what you wanted, I apologize for the confusion.

If you want the SQL from the RCP Parallel Job, could you help me with some pointers on how I could have my job emit the SQL in an RCP job? I am afraid, I don't know the options to enable this.

Thanks again for your time and help,

[VCInDSX]

Ray & Group,
I have some more updates on this issue.

As I have been testing this in 8.0.1 all along, I decided to try this in 7.5.2 to see if it makes any difference. To my surprise, this works without any glitches in 7.5.2.

To clarify, this job design works in all the following tests in a DS Version 7.5.2 by letting the back-end database populate the default column.

1. A Server job that does not have any derivations or entries for the 4th column in the Sequential File Input, Transformer and Target DB stages

2. A NON-RCP Parallel job that does not have any derivations or entries for the 4th column in the Sequential File Input, Transformer and Target DB stages

3. An RCP Parallel job that does not have any derivations or entries for the 4th column in the Sequential File Input, Transformer and Target DB stages and not mentioned in the schema file as well.

NOTE: I tried the parallel jobs using a Copy stage instead of the transformer and it works as expected : Loaded the 3 columns letting the 4th column to be populated by the back-end database.

I am submitting this to IBM support for their comments as well - to understand if this is not supported in 8.0.1 or if it might be something in our server's configuration.

If anyone else has any findings, I would appreciate it if you could share them here.

I will update the group when I get an update from IBM Support.

Thanks again for your time and patience,

[VCInDSX]

Hi Group,
Just to update you on the status of this question.

After a few iterations with the IBM Support group, this is finally entered as a regression ECase as a feature working in a previous version 7.5.2 not working in the current version 8.0.1.

The support team confirmed that they were able to reproduce this issue on both Windows and Linux environment.

I will keep the group posted on any further feedback from the provider.

Thanks

[VCInDSX]

Hi Group,
At last got a patch from IBM Support for this issue.
The ECase reference is E119230.

The limitation at this time is that this patch only fixes the ODBC Enterprise stage.
We had this issue when using the SQL Server enterprise stage and that is still not fixed and is addressed by a separate ECase. I will update the group if a patch gets to us.

Right now we have decided to use the ODBC stage for our jobs.

With this patch, one should be able to load data from an input sequential file that does not have the same number of columns as the target database table using the ODBC Enterprise stage.

[le thuong]

Hi Group,
At last got a patch from IBM Support for this issue.
The ECase reference is E119230.

The limitation at this time is that this patch only fixes the ODBC Enterprise stage.
We had this issue when using the SQL Server enterprise stage and that is still not fixed and is addressed by a separate ECase. I will update the group if a patch gets to us.

Right now we have decided to use the ODBC stage for our jobs.

With this patch, one should be able to load data from an input sequential file that does not have the same number of columns as the target database table using the ODBC Enterprise stage.

Hi -V,

I got the same warning when trying to insert with SQL Server Enterprise stage:

DataSet ==> Copy Stage ==> SQL Server Enterprise

Dataset contains n columns, SQL Server table contains n columns + Id column (defined as identity).

We have Datastage 8.1 (Windows Server 2003).

As a workaround, I replace SQL Server Enterprise stage by ODBC Enterprise and I get no warning.

How can I use SQL Server Enterprise and solve the warning issue ?

[chulett]

The ODBC stage is the only stage that doesn't seem to care that all columns get bound into the sql, all others do. So you need to make sure all columns are bound by not mentioning the ID column in the stage and then using user-defined sql which uses all columns from the stage plus the 'identity' column.