Sub String in DS UV Query

[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.

[Yuan_Edward]

The standard SQL syntax works:

SELECT @ID, SUBSTRING(FULL.TEXT FROM 1 FOR 2000), TIMESTAMP FROM RT_LOG217

[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;

[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.

[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,

[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,

[max.madsen]

Congrats Joyce!

Now, someone can mark as resolved?

[chulett]

Seems appropriate - done.

[ray.wurlod]

It would seem that, like James, I have used up my thinking quota for the month.