Accessing db2 column that has name more than 18 characters

[saivenkat]

Hi Everyone,

Error below throwing while accessing the db2 table 

ERROR: This DBMS table or view cannot be accessed by the SAS System because it contains column names that are not unique when a SAS normalized (uppercased) compare is performed.  See "Naming Conventions" in the SAS/ACCESS documentation.

Analysis:

DB2 table has got 2 different columns having names as CAUSALPARTNOPREFIX, and CAUSALPARTNOPREFIXWID. The column CAUSALPARTNOPREFIXWID length exceeding 18 characters so SAS reading first 18 characters only. 

SAS reading the same column until last week without any issues, and we have the issue above beginning of 19th Nov 2018. 

We have 2 different environments qa and production. Above issue persists with production environment only, so I did compare the environments using the steps below

%macro cmp(command);
filename p pipe &command lrecl=32767;
data _null_;
infile p;
input;
put _infile_;
run;
%mend;
option LS=256;
%cmp("set");

And noticed one of the environment variables IBM_DB_HOME missing in production, added it to the sasuser and ensure it appear back in production but it didn't work

tried to execute the below in both the environments for comparison but no success yet.

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

proc sql;
describe table tstpro.claimjob_ccd;
quit;

ACCESS ENGINE:  Exiting dbidsci with ERROR, rc=0x80121023

production environment showing the above error, but no details available on web to explore

Any tips to debug further would be appreciated?

Thanks,

[Kurt_Bremser]

From where do you get that it fails at 18 bytes, and not at 16?

I ask because SAS has a limit of 32 characters for names, and I have the suspicion that someone used unicode for the column names, causing hex 00's to be inserted after every character.

[saivenkat]

Hi,

The table CLAIMJOB_CCD has 59 columns, out of which only the column name CAUSALPARTNOPREFIXWID exceeding the length of 18 characters and other column exists with the name CAUSALPARTNOPREFIX so the access issue persists against this table.

SQL pass through working fine but ignoring the column CAUSALPARTNOPREFIXWID with the warning below

PROC SQL ;
connect to db2 as db1 (database=xx USER=xxx PASSWORD="{SAS002}xxx" );
create table test as
select *
from connection to db1 (SELECT * FROM xxx.CLAIMJOB_CCD FETCH FIRST 3 ROWS ONLY); 
disconnect from db1;
quit;

WARNING: Variable CAUSALPARTNOPREFIX already exists on file WORK.TEST.

NOTE: Table WORK.TEST created, with 3 rows and 58 columns.

However, same table can be accessed directly from DB2 client environment(client(SAS EG/DI) mac and SAS Compute mac)  without any issues. Other SAS environment(QA) not having any issues while accessing the same table, which pointed to same data source name so I suspect some problem with SAS environment(production) but not able to pin point where the problem is

Thanks,

[kiranv_]

@Kurt_Bremser explains clearly what is happening. SAS/Access truncates column names to length 32 and if you two columns whose names are close, after truncation they become same. I saw this happening when I was using hive tables and using SAS/Access. this is what you can do.

instead of select * you can try below in pass through

select col, col2, CAUSALPARTNOPREFIXWID as newcol, CAUSALPARTNOPREFIX as newcol2

from your table

or in datastep try

data saslib.yourtable;

set db2lib.yourtable(rename= (CAUSALPARTNOPREFIXWID = newcol CAUSALPARTNOPREFIX = newcol2))

;

run;

[saivenkat · Accepted solution]

issue was resolved by upgrading the db2 client in mac, where sas compute installed