Connecting SQL 2008 R2 with Vectorwise

[rocaver]

Hi guys,

I am using PDI 4.2. I am trying to import some tables from SQL 2008 R2 to Ingres VectorWise. I got the connection to SQL and VectorWise OK. I create the table in Vectorwise OK, but when I run the transformation it fails.

I have added the select values step to define friendly data types for Ingres, but still the same. Is the connection possible?

Any help?

Regards,

Benjamin

[marabu]

I got the connection to SQL and VectorWise OK.

So you can use the PDI database explorer without problems?
You can preview some rows using Table Input for both connections?

I create the table in Vectorwise OK

Using PDI for this?

when I run the transformation it fails.

Prove it! Show the log entries.

I have added the select values step to define friendly data types for Ingres, but still the same.

You have what? There are unfriendly datatypes, too?

Is the connection possible?

You already were positive about that in your first words.

I don't have access to a VectorWise instance, but since Actian is listed as a Pentaho technology partner I'm quite positive, that VW is supported.
VW bulk loading only was introduced in PDI 4.2, I understand.
If you suspect PDI to be flawed, try the latest version.

[rocaver]

Hi marabu,

For some reason I do not know, with PDI 4.2 the connection to VectorWise and the explore database works. In version PDI 4.3 it doesn´t. I can retrieve rows from SQL ok, but no rows from Vectorwise, I can only explore the connection. So the connection for both works, but the record retrieve only works for SQl.

Regards, Ben

Just for you to have, I am sending you the log of the error I get:
2012/08/05 10:26:31 - Transformation metadata - The shared object fie [null] is empty!
2012/08/05 10:26:31 - Transformation metadata - We have 2 connections...
2012/08/05 10:26:31 - Transformation metadata - Looking at connection #0
2012/08/05 10:26:31 - Transformation metadata - Looking at connection #1
2012/08/05 10:26:31 - Transformation metadata - Reading 3 steps...
2012/08/05 10:26:31 - Transformation metadata - Looking at step #0
2012/08/05 10:26:31 - Transformation metadata - Looking at step #1
2012/08/05 10:26:31 - Transformation metadata - Looking at step #2
2012/08/05 10:26:31 - Transformation metadata - We have 2 hops...
2012/08/05 10:26:31 - Transformation metadata - Looking at hop #0
2012/08/05 10:26:31 - Transformation metadata - Looking at hop #1
2012/08/05 10:26:31 - Transformation metadata - nr of steps read : 3
2012/08/05 10:26:31 - Transformation metadata - nr of hops read : 2
2012/08/05 10:26:31 - Spoon - Transformation opened.
2012/08/05 10:26:31 - Spoon - Launching transformation [Importar_SQL_Vectorwise]...
2012/08/05 10:26:31 - Spoon - Started the transformation execution.
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Dispatching started for transformation [Importar_SQL_Vectorwise]
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Nr of arguments detected:0
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - This is not a replay transformation
2012/08/05 10:26:31 - Transformation metadata - Natural sort of steps executed in 0 ms (3 time previous steps calculated)
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - I found 3 different steps to launch.
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Allocating rowsets...
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Allocating rowsets for step 0 --> Table input
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - prevcopies = 1, nextcopies=1
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Transformation allocated new rowset [Table input.0 - Select values.0]
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Allocated 1 rowsets for step 0 --> Table input
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Allocating rowsets for step 1 --> Select values
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - prevcopies = 1, nextcopies=1
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Transformation allocated new rowset [Select values.0 - Table output.0]
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Allocated 2 rowsets for step 1 --> Select values
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Allocating rowsets for step 2 --> Table output
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Allocated 2 rowsets for step 2 --> Table output
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Allocating Steps & StepData...
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Transformation is about to allocate step [Table input] of type [TableInput]
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Step has nrcopies=1
2012/08/05 10:26:31 - Table input.0 - distribution activated
2012/08/05 10:26:31 - Table input.0 - Starting allocation of buffers & new threads...
2012/08/05 10:26:31 - Table input.0 - Step info: nrinput=0 nroutput=1
2012/08/05 10:26:31 - Table input.0 - output rel. is 1:1
2012/08/05 10:26:31 - Table input.0 - Found output rowset [Table input.0 - Select values.0]
2012/08/05 10:26:31 - Table input.0 - Finished dispatching
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Transformation has allocated a new step: [Table input].0
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Transformation is about to allocate step [Select values] of type [SelectValues]
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Step has nrcopies=1
2012/08/05 10:26:31 - Select values.0 - distribution activated
2012/08/05 10:26:31 - Select values.0 - Starting allocation of buffers & new threads...
2012/08/05 10:26:31 - Select values.0 - Step info: nrinput=1 nroutput=1
2012/08/05 10:26:31 - Select values.0 - Got previous step from [Select values] #0 --> Table input
2012/08/05 10:26:31 - Select values.0 - input rel is 1:1
2012/08/05 10:26:31 - Select values.0 - Found input rowset [Table input.0 - Select values.0]
2012/08/05 10:26:31 - Select values.0 - output rel. is 1:1
2012/08/05 10:26:31 - Select values.0 - Found output rowset [Select values.0 - Table output.0]
2012/08/05 10:26:31 - Select values.0 - Finished dispatching
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Transformation has allocated a new step: [Select values].0
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Transformation is about to allocate step [Table output] of type [TableOutput]
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Step has nrcopies=1
2012/08/05 10:26:31 - Table output.0 - distribution activated
2012/08/05 10:26:31 - Table output.0 - Starting allocation of buffers & new threads...
2012/08/05 10:26:31 - Table output.0 - Step info: nrinput=1 nroutput=0
2012/08/05 10:26:31 - Table output.0 - Got previous step from [Table output] #0 --> Select values
2012/08/05 10:26:31 - Table output.0 - input rel is 1:1
2012/08/05 10:26:31 - Table output.0 - Found input rowset [Select values.0 - Table output.0]
2012/08/05 10:26:31 - Table output.0 - Finished dispatching
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Transformation has allocated a new step: [Table output].0
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - This transformation can be replayed with replay date: 2012/08/05 10:26:31
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Initialising 3 steps...
2012/08/05 10:26:31 - Table input.0 - Running on slave server #0/1.
2012/08/05 10:26:31 - Table output.0 - Running on slave server #0/1.
2012/08/05 10:26:31 - SQL - rcc - New database connection defined
2012/08/05 10:26:31 - Select values.0 - Running on slave server #0/1.
2012/08/05 10:26:31 - Vectorwise - Rcc - New database connection defined
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : An error occurred intialising this step:
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error occured while trying to connect to the database
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error connecting to database: (using class com.ingres.jdbc.IngresDriver)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Target connection string improperly formatted.
2012/08/05 10:26:31 - SQL - rcc - Connected to database.
2012/08/05 10:26:31 - SQL - rcc - Statement canceled!
2012/08/05 10:26:31 - SQL - rcc - Auto commit off
2012/08/05 10:26:31 - SQL - rcc - Statement canceled!
2012/08/05 10:26:31 - Table input.0 - Connected to database...
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error initializing step [Table output]
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Step [Table input.0] initialized flawlessly.
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - Step [Select values.0] initialized flawlessly.
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Step [Table output.0] failed to initialize!
2012/08/05 10:26:31 - Table input.0 - Finished reading query, closing connection.
2012/08/05 10:26:31 - SQL - rcc - Commit on database connection [SQL - rcc]
2012/08/05 10:26:31 - SQL - rcc - Connection to database closed!
2012/08/05 10:26:31 - Table output.0 - Signaling 'output done' to 0 output rowsets.
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unexpected error rolling back the database connection.
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unable to get database metadata from this database connection
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run (null:-1)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.ui.spoon.trans.TransGraph$22.run (TransGraph.java:3659)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.Trans.prepareExecution (Trans.java:766)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose (TableOutput.java:697)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.rollback (Database.java:717)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.rollback (Database.java:728)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.getDatabaseMetaData (Database.java:3139)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.getDatabaseMetaData(Database.java:3143)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.rollback(Database.java:728)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.rollback(Database.java:717)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:697)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:766)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.ui.spoon.trans.TransGraph$22.run(TransGraph.java:3659)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.lang.NullPointerException
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.getDatabaseMetaData(Database.java:3139)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 6 more
2012/08/05 10:26:31 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Importar_SQL_Vectorwise: preparing transformation execution failed
2012/08/05 10:26:31 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleException:
2012/08/05 10:26:31 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : We failed to initialize at least one step. Execution can not begin!
2012/08/05 10:26:31 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
2012/08/05 10:26:31 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
2012/08/05 10:26:31 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:792)
2012/08/05 10:26:31 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.ui.spoon.trans.TransGraph$22.run(TransGraph.java:3659)
2012/08/05 10:26:31 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
2012/08/05 10:26:31 - Importar_SQL_Vectorwise - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!

[marabu]

So the connection for both works, but the record retrieve only works for SQl.

2012/08/05 10:26:31 - Vectorwise - Rcc - New database connection defined
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : An error occurred intialising this step:
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error occured while trying to connect to the database
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error connecting to database: (using class com.ingres.jdbc.IngresDriver)
2012/08/05 10:26:31 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Target connection string improperly formatted.

See the bold formatted parts?
Can you explain the contradiction?

[rocaver]

With Version 4.2 You can connect to the datasource database and can use the object explorer to go around vectorwise objects. But when you use the step table input with these Datasource, it fails. For me there is no contradiction, the connection works but the table input step does not.

In version 4.3 I cant even establish a connection.

Thanks, Ben

[marabu]

Please, give your obfuscated connection string with all parts in place.

BTW: You talk about table input not working, but the log is about "Table output"...

[rocaver]

Hi guys,

In PDI 4.2 connection with vectorwise works ok and in PDI 4.3 it doesn't. I am attaching the job and transformations I am using to load data into Vectorwise.The only command that is not working for me is the COPY TABLE from within a SQL script to load the data into Vectorwise, other commands such as create table or index work ok.

This Copy Table command works ok from within the SQL tool from vectorwise.

On the other hand, connecting SQL with Ingres via JDBC works ok and transfers data ok also, but is extremely slow.

Any help will be appreciated.

Regards,

Ben

[rocaver]

In PDI 4.2 connection with vectorwise works ok and in PDI 4.3 it doesn't.
Ben

This is because the PDI 4.3 version I dl from sourceforge didnt come with the Ingres JDBC Driver. I copied the driver into the /libext/jdbc folder and now I can connect ok with Ingres Vectorwise.

[marabu]

First things first:

The only command that is not working for me is the COPY TABLE from within a SQL script to load the data into Vectorwise ...
This Copy Table command works ok from within the SQL tool from vectorwise.

This is expected behaviour: http://docs.actian.com/ingres-vector...uide/5317-copy

COPY Statement
...
Valid in: SQL, ESQL, OpenAPI

Now the fun part:

On the other hand, connecting SQL with Ingres via JDBC works ok and transfers data ok also, but is extremely slow.

Have you ever tried to use the PDI bulk loading (hint) support for Vectorwise?

[rocaver]

I have read somewhere that the bulk step uses pipes, and that only runs in Linux.

On the other hand I didnt't quite get the issue with:

Originally Posted by actian
COPY Statement
...
Valid in: SQL, ESQL, OpenAPI


Does this mean that I cant execute this statement from a SQL script? ever?

Regards,

Ben