Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: slowly changing dim on MSSQL 2005

  1. #1

    Default slowly changing dim on MSSQL 2005

    Hi,

    i m storing my dataWarehouse in a MSSQL Server 2005. My transformation and jobs are running just perfectly for month. But if one of the slowly changing dimension tables reaching a amount of 730-800 records Kettle crashes because of a Exception of the jdbc-driver.
    I already tied to use jtds, Microsoft JDBC Connector for MS SQL Server 2005 and Sprinta, latest versions as jdbc-driver. But all of them show the same behaviour.
    The exception is thrown when kettle tries to insert a new dimension record

    root-cause from stack-trace
    2007/10/29 12:36:41 - Kunde zurückschreiben.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement has to be executed first before rows can be returned.
    2007/10/29 12:36:41 - Kunde zurückschreiben.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
    2007/10/29 12:36:41 - Kunde zurückschreiben.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(Unknown Source)
    2007/10/29 12:36:41 - Kunde zurückschreiben.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.getGeneratedKeys(Database.java:1329)
    2007/10/29 12:36:41 - Kunde zurückschreiben.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : ... 4 more
    Last edited by xCojotex; 10-29-2007 at 08:06 AM.

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Weird. Even a Google search for the error message turns up empty.
    Can you give version 2.5.2 a try?

    Maybe it's something stupid. You do have an auto-increment column in the target table, right?

  3. #3

    Default

    Hi Matt,

    thank you verry much for your answer.
    I tried PDI 2.5.2 (by the way: really nice installer!) now but same error there. The transformation runs since 3 months without any problems. The table contains 750 records inserted by the same kettle transformation that cause the problems now. I don't think it s a kettle problem, perhaps SQL Server related or jdbc driver related bug?

    The ddl to create the table:
    CREATE TABLE [dbo].[dim_kunde] (
    [kunde_id] [bigint] IDENTITY (0, 1) NOT NULL ,
    [version] [int] NULL ,
    [gueltigAb] [datetime] NULL ,
    [gueltigBis] [datetime] NULL ,
    [liveKey] [varchar] (36) COLLATE Latin1_General_CI_AS NULL ,
    [kdnr] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
    [name1] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
    [name2] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
    [strasse] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
    [land] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [plz] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
    [ort] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
    [telefon] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
    [branche] [varchar] (40) COLLATE Latin1_General_CI_AS NULL
    ) ON [PRIMARY]
    If kettle tries to insert a new record now the exception gets thrown. If i take a look at the table no new record has been inserted. But if i insert a record using SQL Manager i can proof that the primary key has been incremented by the failed kettle insert operation.

    This is the stack trace using jtds-jdbc driver:
    Because of an error this step can't continue:
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : Unable to retrieve value of auto-generated technical key : unexpected error:
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.run (DimensionLookup.java:751)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.processRow (DimensionLookup.java:662)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.lookupValues (DimensionLookup.java:298)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.core.database.Database.dimInsert (Database.java:1289)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : be.ibridge.kettle.core.exception.KettleDatabaseException:
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : Unable to retrieve value of auto-generated technical key : unexpected error:
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.run (DimensionLookup.java:751)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.processRow (DimensionLookup.java:662)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.lookupValues (DimensionLookup.java:298)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.core.database.Database.dimInsert (Database.java:1289)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) :
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.core.database.Database.dimInsert(Database.java:1300)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.lookupValues(DimensionLookup.java:298)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:662)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.trans.step.dimensionlookup.DimensionLookup.run(DimensionLookup.java:751)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : Caused by: java.lang.NullPointerException
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : at be.ibridge.kettle.core.database.Database.dimInsert(Database.java:1289)
    2007/10/29 14:38:27 - Kunde zurückschreiben.0 - ERROR (version 2.5.2, build 252005 from 2007/10/23 19:31:44) : ... 3 more
    Last edited by xCojotex; 10-29-2007 at 09:41 AM.

  4. #4

    Default

    Today i have had some time to download Kettle 2.5 source and to have a debug session to locate my problem.

    I have set some breakpoint in database.java getGeneratedKeys(Prepared Statement s) method. I have seen that the return auto_generated keys property in the preparedStatementInsert is set to false before the statement gets executed.

    if (keyfield==null||keyfield.equals("kunde_id"))
    {
    log.logDetailed(toString(), "SQL w/ return keys=["+sql+"]");
    prepStatementInsert=connection.prepareStatement(databaseMeta.stripCR(sql), Statement.RETURN_GENERATED_KEYS);
    }
    else
    {
    log.logDetailed(toString(), "SQL=["+sql+"]");
    prepStatementInsert=connection.prepareStatement(databaseMeta.stripCR(sql));
    }
    i added keyfield.equals("kunde_id") to if(keyfield==null) and now my transformations are running again.
    Is this because i have set the technical key field in the Dimension Insert Update Step? Does this mean that i have to leave this field blank if the property use auto increment is set? But if so why has it worked for months with this settings?


    Thank you for your answer
    PS: kunde_id is the name of my pk column
    If i leave the technical key field blank in Dimension insert/update keyfield is String "" and not null so second condition gets evaluated too
    Last edited by xCojotex; 11-02-2007 at 09:29 AM.

  5. #5

    Default

    The problem seems to be in DimensionLookup.java in conjunction with the preparedStatement getting constructed only once in dim_insert.

    My transformation was running since month because there were no runs with a update before the first insert.
    I think the problem is that keyfield has to be null to retrieve the pk. But if we have to update a dimension record the keyfield is not null.
    So if update is the first action done, the update and inserPreparedStatement were constructed with false where probably true is required for the insertStatement.

    Sorry for writing in the forum and not using Jira instead but i m not sure if this is a bug.
    Last edited by xCojotex; 11-02-2007 at 10:15 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.