Importing meta data from sybase IQ

roy · Post by **roy** » Tue Dec 07, 2004 9:10 am

Hi All,
does anyone know how to get table definitions from sybase IQ?
let me rephrase, does anyone have this working???

DS & sybaseIQ on same machine.

using 3rd party odbc crashes the manager on general error.

data direct odbc not compatible with IQ.

only temporary solution we have is to use a windows DS server to get table definitions (using 3rd party/sybase driver odbc for windows) then export/import to the unix DS server.

Thanks in advance,

chulett · Post by **chulett** » Tue Dec 07, 2004 9:34 am

Can you not use the Sybase OC plugin?

roy · Post by **roy** » Tue Dec 07, 2004 2:43 pm

No Craig it simply doesn't work

chulett · Post by **chulett** » Tue Dec 07, 2004 3:02 pm

Sorry, Oracle Boy should have kept quiet I guess.

I get the impression "Sybase IQ" is different than just plain "Sybase", eh?

roy · Post by **roy** » Tue Dec 07, 2004 3:14 pm

I was told it uses a regular sybase to hold it's repository, but yes it is different.
Ascential says their stage/odbc connections are not Iq compatible, so I'm fishing for implemented solutions.

I even tried the odbc meta brokers, using an existing - working odbc driver from my client; it even seems to work except no new table definition is created

.

if anyone knows of a way except for installing a windows DS server to do the job and import/export to the unix DS server I'd love to hear about it

again thanks in advance,

peternolan9 · Post by **peternolan9** » Mon Dec 13, 2004 9:29 am

Hi Roy,
just by the way what platform are you on? I've used IQ a bit but not with DS, only with INFA.

'Regular Sybase for repository.' Well, no, it uses Sybase ASA which is a really small footprint database. This is what you connect to using ODBC.

Actually, how it works is the 'catalog' is stored in ASA and then all the IQ data is just storted in thumping big files that you don't need to know anything about.

Sybase IQ comes with it's own ODBC driver that you should be able to make visible to DS. And yes, no other drivers and Sybase OC talk to IQ as far as I am aware....Use the IQ loader to load data, not ODBC inserts.

Also, you should only read using the ODBC driver. In IQ individual record operations are slow (like and update or an insert) but huge operations are blindingly fast......and deletes are fast because they are not logged....so in IQ you need to load your data to a temp table then call a stored procedure to perform a delete of the rows that will be loaded and then do a select insert. Though it takes a bit of extra coding, the result is something that runs with just amazing, amazing speed. Way, way faster than do anything else that gets logged...

Also, there were some changes in 12.5.x which I have not used yet and Sybase say that mass updates are as fast as the delete inserts now so you want to try that out as well..

Feel free to email me at peter@peternolan.com if you have more questions...I always wanted a reason to test IQ and DS..

We have both installed on AIX here because we are demoing IQ.

roy wrote:I was told it uses a regular sybase to hold it's repository, but yes it is different.
Ascential says their stage/odbc connections are not Iq compatible, so I'm fishing for implemented solutions.

I even tried the odbc meta brokers, using an existing - working odbc driver from my client; it even seems to work except no new table definition is created .

if anyone knows of a way except for installing a windows DS server to do the job and import/export to the unix DS server I'd love to hear about it

again thanks in advance,

evanmaas · Post by **evanmaas** » Mon Dec 13, 2004 10:20 am

Hi Roy,

I've a working situation on XP with DS 7.5 and Sybase IQ 12.5.

On your client make a ODBC connection with Sybase driver 'Adaptive Server IQ 12 (7.00.04.3517)', not with DataDirect (this is use by Informatica Powercenter). Fill user
and password, servername IQ, databasename IQ, database file.
After the connection you can import IQ table definitions in DS.
Table Definitions> Import> ODBC> IQ DSN-name
Now you get a list of tables defined in IQ, that you can import in DS.

Success.

Regards,

Erik

roy · Post by **roy** » Wed Dec 15, 2004 3:35 pm

Hi,
thanks for the replies.

I see I was missled on the repository issue

.

to sumup:
1. my case is on sun os not windows, I did mention they had it working with DS on windows.

2. for some reason they were not able to make it work using the sybase odbc driver which is on the machine, it crashes the manager.

I'll ask them to follow up this post and add their current situation and problems.

for now I guess support will provide a temporrary serial for windows installation till this issue is resolved.

thanks again,