Avatar of tranicus
tranicus

asked on 

Extract/Load in Sybase ASIQ binary format

Hello All,

I've extracted a table from IQ server using the following query:

set temporary option Temp_Extract_Name1 = '\\\\machine11\\binary_product_item.bin';
set temporary option Temp_Extract_Binary = 'on';
set temporary option Temp_Extract_Column_Delimiter = '      ';

select
  column1
, column2
, column3
, column4
, column5
FROM product_item
>>#junkfile

And load it into a temp table with the load query:

LOAD TABLE #temp_product_item
(
  column1 '\x09'      
, column2 '\x09'      
, column3 '\x09'      
, column4 '\x09'      
, column5 '\x0d\x0a'      
)
FROM
'\\\\machine11\\binary_product_item.bin'
ESCAPES OFF
QUOTES OFF
FORMAT binary
DELIMITED BY '\x09'
ON FILE ERROR FINISH
DELIMITED BY '\x09'
WITH CHECKPOINT ON

I'm constantly getting error while loading - "Number of bytes (512) for a column from and input file as exceeded the maximum allowed(9)".  Has anybody done binary extract and load that not having a problem?

Many Thanks,
Sybase Database

Avatar of undefined
Last Comment
Joe Woodhouse
Avatar of Joe Woodhouse
Joe Woodhouse

I'm confused by you specifying the delimiter as a literal in the "load spec", then in the DELIMITED BY clause (twice!). Isn't it one or the other?

Is there any possibility you have TAB characters in your data?
Avatar of tranicus
tranicus

ASKER

Hello Joe,

The Delimited By noted twice on load statement was my error on cut/paste, please ignore.  Regard to possible in extra tab character.  Good question, I will tested out on general Text extract to see it have the same problem.
Avatar of Joe Woodhouse
Joe Woodhouse

I've looked into this a bit more, and I can see a few potential issues.

Firstly, when loading into a temp table, by default you will lose all rows in it on the next COMMIT unless it was declared using the ON COMMIT PRESERVE ROWS clause.

Secondly, as I thought, you specifiy *either* a delimiter in the column definitions, *or* in a DELIMITED BY clause. It is incorrect to do both. Since you also specify a row delimiter after the final column delimiter, I suggest you remove the DELIMITED BY.

Thirdly, I have to say that I've found >75% of all load problems to involve the delimiter character occuring in character data. Try querying the original data to see if a TAB or NEWLINE occurs anywhere in the character columns, and/or specifying different delimiters.

Good luck!
Avatar of tranicus
tranicus

ASKER

Hello Joe,

I emblemented your suggestion into my load script.  I still get the same error....  May I see one of your binary load script as an example?

Thank you,
Tran
ASKER CERTIFIED SOLUTION
Avatar of Joe Woodhouse
Joe Woodhouse

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Sybase Database
Sybase Database

Sybase, a subsidiary of SAP, builds a client/server relational database management system. Products include Adaptive Server Enterprise (ASE), Adaptive Server Anywhere (ASA), Sybase Unwired Platform (SUP) for mobile applications, Afaria for enterprise mobile device management and IQ for data warehouse and big data applications.

5K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo