Sub String in DS UV Query
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
Sub String in DS UV Query
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.
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.
Edward Yuan
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
Re: Sub String in DS UV Query
The standard SQL syntax works:
SELECT @ID, SUBSTRING(FULL.TEXT FROM 1 FOR 2000), TIMESTAMP FROM RT_LOG217
SELECT @ID, SUBSTRING(FULL.TEXT FROM 1 FOR 2000), TIMESTAMP FROM RT_LOG217
Edward Yuan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Or you can override the format.
The UniVerse/SQL syntax requires a SUBSTRING function.
Code: Select all
SELECT SUBSTRING(FULL.TEXT FROM 1 FOR 1000) FROM RT_LOG217;
Code: Select all
SELECT FULL.TEXT FMT 'L#1000' FROM RT_LOG217;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
I've always tried to set MAXFETCHBUFFSIZE as a power of 2, but I can't recall why, Perhaps try it with 16384.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
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,
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,
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
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,
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,
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil
-
- Premium Member
- Posts: 62
- Joined: Tue Dec 07, 2004 7:41 am
- Location: Brasil
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: