topic Re: Crating DB2 table in SAS Data Management

Creating DB2 table

JoaoM — Thu, 31 Mar 2016 14:08:28 GMT

I'm trying to create a table in DB2, but the variable has 14 positions.
With variable with size below 9 I have no problems.
But when caught variables above that length I get the following error:

ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "("
was found following "". Expected tokens may include: "FIELDPROC CHECK GENERATED IMPLICITLY INLINE CONSTRAINT PRIMARY".
SQLSTATE=42601 .

Code:

proc sql;
    create table db2lib.test2 as
    select
     var1
from
        work.test ;
quit;

max size of var1 is 99999999911119.

Re: Crating DB2 table

Kurt_Bremser — Thu, 31 Mar 2016 14:01:15 GMT

Did you try

data db2lib.test2;
set work.test;
keep var1;
run;

Re: Crating DB2 table

JoaoM — Thu, 31 Mar 2016 14:07:49 GMT

Same error

Re: Crating DB2 table

LinusH — Thu, 31 Mar 2016 14:53:11 GMT

Is it numerical or char?

If numerical, you may hit the default max size for the mapped data type in DB2.

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

Re: Crating DB2 table

Kurt_Bremser — Fri, 01 Apr 2016 05:31:59 GMT

It looks like the column is (per default) defined as integer in DB/2, where you have a range of -2147483648 to +2147483647.

One thing that came to my mind was to use PROC DS2 and define the column as BIGINT or FLOAT.

Re: Crating DB2 table

LinusH — Fri, 01 Apr 2016 10:35:44 GMT

When numerical variable become this large, on can ask if they really need to be integers - are they par of calculations?

Another option is to use data set options to explicitly specify data types in the target database (DBTYPE).