ODBC Connector Stage - Partition Reads Error

manish1005 · Post by **manish1005** » Thu Oct 25, 2007 7:59 am

Hi,

I am trying to read data from a ODBC Connector Stage. Job design is simple with ODBC stage as source and sequential file as target. It runs fine for normally but when I turn on 'Enable Partition Reads' feature of ODBC Stage and give the table name & column name it throws following error.

The source table is a simple table on postgres database.
Apt configuration file details are there in the log below.

................................
...................................
Item #: 8
Event ID: 268
Timestamp:: 2007-10-25 18:36:57
Type: Info
User Name: dsadm
Message:: ODBC_Connector_0: [IIS-CONN-ODBC-000105] Connected to PostgreSQL, version 08.01.0200 PostgreSQL 8.2.4 (Greenplum Database 3.0.0.0-beta build 6 build ) on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4 through driver libpsqlodbc.so.
[IIS-CONN-DAAPI-000364] Generated SELECT statement: SELECT partkey,name,mfgr,brand,size,container,retailprice FROM manish_part3

Item #: 9
Event ID: 269
Timestamp:: 2007-10-25 18:36:57
Type: Info
User Name: dsadm
Message:: main_program: APT configuration file: /opt/IBM/InformationServer/Server/Configurations/default.apt
{
node "node1"
{
fastname "greenplumbtest.persistent.co.in"
pools ""
resource disk "/opt/IBM/InformationServer/Server/Datasets" {pools ""}
resource scratchdisk "/opt/IBM/InformationServer/Server/Scratch" {pools ""}
}
}

Item #: 10
Event ID: 270
Timestamp:: 2007-10-25 18:37:05
Type: Info
User Name: dsadm
Message:: ODBC_Connector_0: [IIS-CONN-ODBC-000091] Before SQL command executed successfully

Item #: 11
Event ID: 271
Timestamp:: 2007-10-25 18:37:05
Type: Info
User Name: dsadm
Message:: ODBC_Connector_0,0: [IIS-CONN-ODBC-000105] Connected to PostgreSQL, version 08.01.0200 PostgreSQL 8.2.4 (Greenplum Database 3.0.0.0-beta build 6 build ) on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4 through driver libpsqlodbc.so.
[IIS-CONN-DAAPI-000364] Generated SELECT statement: SELECT partkey,name,mfgr,brand,size,container,retailprice FROM manish_part3

Item #: 12
Event ID: 272
Timestamp:: 2007-10-25 18:37:05
Type: Fatal
User Name: dsadm
Message:: ODBC_Connector_0,0: [IIS-CONN-DAAPI-000086] Partitioning trange too nerrow (0, 0) (CC_DBRecordDataSetProducer::modifySqlForRangePartitioning, file /builds/Common_Connectors/Build_Current/CC_Common/DAAPI/build/include/ccl/connector/CC_DBRecordDataSetProducer.inl, line 573)

Item #: 13
Event ID: 273
Timestamp:: 2007-10-25 18:37:05
Type: Fatal
User Name: dsadm
Message:: ODBC_Connector_0,0: The runLocally() of the operator failed.

Item #: 14
Event ID: 274
Timestamp:: 2007-10-25 18:37:05
Type: Info
User Name: dsadm
Message:: ODBC_Connector_0,0: Output 0 produced 0 records.

Item #: 15
Event ID: 275
Timestamp:: 2007-10-25 18:37:05
Type: Fatal
User Name: dsadm
Message:: ODBC_Connector_0,0: Operator terminated abnormally: runLocally did not return APT_StatusOk

Item #: 16
Event ID: 276
Timestamp:: 2007-10-25 18:37:06
Type: Info
User Name: dsadm
Message:: Sequential_File_3,0: Export complete; 0 records exported successfully, 0 rejected.

Item #: 17
Event ID: 277
Timestamp:: 2007-10-25 18:37:11
Type: Fatal
User Name: dsadm
Message:: main_program: APT_PMsectionLeader(1, node1), player 2 - Unexpected exit status 1.

Item #: 18
Event ID: 278
Timestamp:: 2007-10-25 18:37:15
Type: Info
User Name: dsadm
Message:: ODBC_Connector_0: [IIS-CONN-ODBC-000092] After SQL command executed successfully

Item #: 19
Event ID: 279
Timestamp:: 2007-10-25 18:37:15
Type: Fatal
User Name: dsadm
Message:: main_program: Step execution finished with status = FAILED.

Item #: 20
Event ID: 280
Timestamp:: 2007-10-25 18:37:15
Type: Info
User Name: dsadm
Message:: main_program: Startup time, 0:21; production run time, 0:01.

Please suggest.

ArndW · Post by **ArndW** » Thu Oct 25, 2007 7:41 pm

what happens when you use your favorite access tool to run the command "SELECT partkey,name,mfgr,brand,size,container,retailprice FROM manish_part3 ;" - does that also generate an error? Also, did you cut-and-paste the following error message from the log "ODBC_Connector_0,0: [IIS-CONN-DAAPI-000086] Partitioning trange too nerrow (0, 0)"? <sic>

ray.wurlod · Post by **ray.wurlod** » Thu Oct 25, 2007 10:01 pm

Try forcing the ODBC Connector to execute in sequential mode, so that partitioning is not at issue. If that works, then the attempt to partition the data from source would appear to be the culprit.

manish1005 · Post by **manish1005** » Thu Oct 25, 2007 11:43 pm

When I run the query 'SELECT partkey,name,mfgr,brand,size,container,retailprice FROM manish_part3 ' on my Database client tool it correctly fetches me 1012 records.

Regarding the ODBC_"Partitioning trange too nerrow (0, 0)" error, i cut and pasted the exact log as seen from Director. I also could not understand why the words are not spelled right in the message?

If I set 'Enable Partition Reads' to No, in ODBC Connector stage my job runs fine.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 26, 2007 1:23 am

... which is what I suggested you do. I suggest further that PostGres (or at least the ODBC driver for PostGres) does not support partitioned reads.

manish1005 · Post by **manish1005** » Fri Oct 26, 2007 6:19 am

This time I tried the same thing with DB2 v8. Here again, without partition reads jobs works fine and with partition reads the job fails. I am pasting the error log for Db2: I am using data direct driver for db2.

Item #: 11
Event ID: 361
Timestamp:: 2007-10-26 17:33:31
Type: Info
User Name: dsadm
Message:: ODBC_Connector_0,0: [IIS-CONN-ODBC-000105] Connected to UDB DB2 for Windows, UNIX, and Linux, version 08.23.0000 through driver VMdb222.so.
[IIS-CONN-DAAPI-000364] Generated SELECT statement: SELECT "partkey","name","mfgr","brand","size","container","retailprice" FROM "manish_part3"

Item #: 12
Event ID: 362
Timestamp:: 2007-10-26 17:33:31
Type: Fatal
User Name: dsadm
Message:: ODBC_Connector_0,0: [IIS-CONN-DAAPI-000086] Partitioning trange too nerrow (0, 0) (CC_DBRecordDataSetProducer::modifySqlForRangePartitioning, file /builds/Common_Connectors/Build_Current/CC_Common/DAAPI/build/include/ccl/connector/CC_DBRecordDataSetProducer.inl, line 573)

Item #: 13
Event ID: 363
Timestamp:: 2007-10-26 17:33:31
Type: Fatal
User Name: dsadm
Message:: ODBC_Connector_0,0: The runLocally() of the operator failed.

Item #: 14
Event ID: 364
Timestamp:: 2007-10-26 17:33:31
Type: Info
User Name: dsadm
Message:: ODBC_Connector_0,0: Output 0 produced 0 records.

Item #: 15
Event ID: 365
Timestamp:: 2007-10-26 17:33:31
Type: Fatal
User Name: dsadm
Message:: ODBC_Connector_0,0: Operator terminated abnormally: runLocally did not return APT_StatusOk

Item #: 16
Event ID: 366
Timestamp:: 2007-10-26 17:33:32
Type: Info
User Name: dsadm
Message:: Sequential_File_3,0: Export complete; 0 records exported successfully, 0 rejected.

Item #: 17
Event ID: 367
Timestamp:: 2007-10-26 17:33:37
Type: Fatal
User Name: dsadm
Message:: main_program: APT_PMsectionLeader(1, node1), player 2 - Unexpected exit status 1.

Item #: 18
Event ID: 368
Timestamp:: 2007-10-26 17:33:37
Type: Info
User Name: dsadm
Message:: ODBC_Connector_0: [IIS-CONN-ODBC-000092] After SQL command executed successfully

Item #: 19
Event ID: 369
Timestamp:: 2007-10-26 17:33:37
Type: Fatal
User Name: dsadm
Message:: main_program: Step execution finished with status = FAILED.

The values which I am setting for partition reads are:
Enable Partitioned reads = yes
Partition reads method - Min/Max range
Table name - manish_part3
Column name - manish_part3.partkey

I am not sure what properties are set at db2 end.
Isn't it supposed to be transparent from database point of view, if its firing two or more queries with different where clause to the database on same or different connection?
[/img]

sjfearnside · Post by **sjfearnside** » Wed Mar 18, 2009 10:43 am

I am getting the same error, Partitioning trange too nerrow (0, 0). Was this ever resolved? If so, what was the solution?