Page 1 of 1
Sub String in DS UV Query
Posted: Wed Nov 08, 2006 5:36 pm
by Yuan_Edward
The Original Query is: SELECT @ID, FULL.TEXT, TIMESTAMP FROM RT_LOG217, And my job got the following error:
ETL_DS_JOB_LOG.SBL_ODS_CUSTOMER_ACCOUNT.tLoad.Uni_RT_LOGS_inp: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:SELECT @ID, FULL.TEXT, TIMESTAMP FROM RT_LOG217
SQLSTATE=S1000, DBMS.CODE=930122
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: Row length exceeds buffer size.
The columns definition is:
@ID VARCHAR(10) NULLABLE TYPE S
FULL.TEXT VARCHAR(2000) NULLABLE TYPE S
TIMESTAMP VARCHAR(254) NULLABLE TYPE S
Probably it's because FULL.TEXT is too long. So I am wondering if there is a way to truncate FULL.TEXT to 2000.
I am not aware of the UV syntax. Any comments are appreciated.
Re: Sub String in DS UV Query
Posted: Wed Nov 08, 2006 6:03 pm
by Yuan_Edward
The standard SQL syntax works:
SELECT @ID, SUBSTRING(FULL.TEXT FROM 1 FOR 2000), TIMESTAMP FROM RT_LOG217
Posted: Thu Nov 09, 2006 8:34 am
by ray.wurlod
You can edit your
uvodbc.config file to set MAXFETCHBUFFSIZE to a value larger than its default of 8192 bytes.
The UniVerse/SQL syntax requires a SUBSTRING function.
Code: Select all
SELECT SUBSTRING(FULL.TEXT FROM 1 FOR 1000) FROM RT_LOG217;
Or you can override the format.
Code: Select all
SELECT FULL.TEXT FMT 'L#1000' FROM RT_LOG217;
Posted: Fri Nov 11, 2011 2:31 pm
by ray.wurlod
Adding it as a separate line is correct. What was the "different error"?
I've always tried to set MAXFETCHBUFFSIZE as a power of 2, but I can't recall why, Perhaps try it with 16384.
Posted: Fri Nov 11, 2011 2:36 pm
by joycerecacho
Ray,
I wrote in the uvodbc.config:
[ODBC DATA SOURCES]
<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1
MAXFETCHBUFFSIZE = 16384
The error when I read my UV Query now is:
DSD.BCIConnect connecting to localuv, call to SQLConnect failed.
SQLSTATE=IM997, DBMS.CODE=0
[DataStage][SQL Client]An illegal configuration option was found
Do u know what is happening?
Best Regards,
Posted: Wed Nov 16, 2011 10:18 am
by joycerecacho
Hello Guys!!!
I found what was wrong!!!!!!!
The parameter name was invalid actually.
The parameter name is "MAXFETCHBUFF" and not "MAXFETCHBUFFSIZE"!!
Now my job is running OK.
Thanks for your help, Ray.
My uvodbc.config is:
[ODBC DATA SOURCES]
<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1
MAXFETCHBUFF = 16384
Best Regards,
Posted: Wed Nov 16, 2011 10:21 am
by max.madsen
Congrats Joyce!
Now, someone can mark as resolved?
Posted: Wed Nov 16, 2011 11:48 am
by chulett
Seems appropriate - done.
Posted: Wed Nov 16, 2011 3:03 pm
by ray.wurlod
It would seem that, like James, I have used up my thinking quota for the month.
