Updating Sybase IQ database

nvalia · Post by **nvalia** » Mon Jan 15, 2007 12:29 am

Hi,

Has any one faced any problems when trying to update a Sybase IQ table from Datastage uisng an ODBC stage as it has a Column based architecture and not a row based one like other RDBMS.
Any specific way to handle this, if any?

Regards,
NV

rleishman · Post by **rleishman** » Mon Jan 15, 2007 12:56 am

I've used Sybase IQ, but with Informatica, not DataStage.

The column-based architecture should not be a problem, it should be transparent to SQL. Just issue the same SQL Update statements that you would on any other database.

Now performance WILL be a problem if you have significant volumes; updates on IQ are not exactly speedy. Rather than perform transactional updates, we wrote all rows to a sequential file and then used a T-SQL procedure to load the file into a Local Temporary Table, then performed a set-based update by joining the LTT with the target table. Performance still sucked, but it was appreciably faster.

nvalia · Post by **nvalia** » Mon Jan 15, 2007 1:30 am

Thanks Ross for your prompt response.

We are looking at around 1 TB data volumes..
Also would Delete then Insert instead of update be slow too?

Regards,
NV

rleishman · Post by **rleishman** » Mon Jan 15, 2007 5:23 am

I havent experimented enough with IQ to tell you for sure. I do know that updates and deletes are abysmally slow. Inserts are fine - bulk loads are better.

We got ours running in an acceptable time frame, but the volumes were not crippling.

In Oracle, it is generally faster to truncate (not delete!) and rebuild a table if you are going to update more than 1-10% of the rows (it varies). I dare say IQ would be the same - I assume it has some equivalent of the truncate command.

The problem with the version I used is that it did not support partitioning. I don't know whether the current version does or not. The problem with truncate and rebuild without partitioning is that your percentage of updates or deletes gets smaller and smaller. You ultimately cross the threshold beyond which it is faster to issue and update/delete statement.

I suggest you experiment. Benchmark it with transactional updates from DataStage. Then try bulk updates in an update-join.

kcbland · Post by **kcbland** » Mon Jan 15, 2007 7:42 am

You cannot use DML based inserts and updates with Sybase IQ as performance is TERRIBLE, not even worth considering. You should consider writing a "bulk update" script to take a flat file, ftp/scp it to the sybase server, and run dbisqlc to load the data file into a temp table and then issue a correlated update statement. Delete-then-insert requires all of the same activities because you still have to identify the rows for deletion, so you might as well just update instead of delete-then-insert.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 15, 2007 3:42 pm

This will affect people's answers.

roy · Post by **roy** » Thu Jan 18, 2007 10:37 am

Hi,
Last Time I used sybase IQ (a Few years back) we had DS server.

We prepared bulk loads, concatenated the load scentaces, added commit/rollback and run the actual load by invoking the loader from command line.

worked and still works as fas as I know like a charm.

IHTH, (I Hope This Helps),