We are in process from upgrading our DB2 database from 9.1 to 10.5 fp5 with DB2 Blu acceleration.

A typical focexec would look like this:

TABLE FILE ACCOUNT
PRINT BALANCE CUSTOMER_NAME
BY OPEN_DATE
BY CUSTOMER_ID
ON TABLE HOLD AS M1828CWF1 FORMAT DB2
END

In some cases, it appears to be creating a row organized table and loading the table with no issue.
In other cases, it blows up with an error:
[42858] [IBM] [CLI Driver] [DB2/LINUXX88664] SQL0969N There is no message text corresponding to SQL error "-1667" in the message file on this work station. The error was returned from module "SQLNQ2EA" with original tokens "EDAUSR1.M1828CWF1 ORGANIZE BY COLUMN CREATE INDEX". SQLSTATE-42858

Our DBA says when it works, it creates the table, does the insert, and creates a primary index.
When it fails with that error, it creates the table, does the insert, tries to organize by column and create an index (which I thought he said isn't needed on a columnar table)

My problem is we don't seem to know under what circumstances it tries to create the columnar table. We've had requests where we comment out the BY statements and it works. Other requests have 4 BY statements and loads successfully. The BY statements create unique keys (no duplicates)

Does anyone have experience creating tables in DB2 Blu and can tell me either
1) what the circumstances are when the SQL is translated to try to load a columnar table vs a row organized table
or
2) how to override this - short of writing every step that creates a DB2 table in passthru SQL?

Thanks!

We are running WebFOCUS 8009 on Windows

This message has been edited. Last edited by: FP Mod Chuck,

Dave found the resolution...
The data was being sorted by a numeric field in DB2 Blu and trying to hold it as a DB2 table.

Either sort by an alpha field, don't sort it when putting into DB2, or sort and HOLD it then load into DB2 without a sort.