Skip to Content

Question about using the UNLOAD statement

Hi,

I have a relatively large database I need to rebuild from scratch.   I can't use the dbunload utility because the database is corrupt, so I have to go table by table.

I also thought that since I have to do this, that I should take this oportunity to trim the database and copy only relevant data.   So I thought about using the UNLOAD statement to export a subset of the data.

For example, the table "iTrans":

UNLOAD SELECT * FROM iTrans  WHERE TransDate >= '2014-01-01' TO '\\transfer\itrans.dat";

This works perfect.   The problem is with "iTrans" child table "iTransRow".   The primary key is a two column key (IdLoc, IdSeq).   

HOW do I filter the rows in "iTransRow" to only contain those that are in the "iTrans" subset?   If the the primary key was a single column it would be easy using the IN clause, but with a two column key?

I came up with this, but I think I'm pretty sure there must be a better way...

UNLOAD SELECT * FROM iTransRow

WHERE 

EXISTS (SELECT * FROM iTrans AS A WHERE A.Fecha >= '2014-01-01' AND iTransRow.IdLoc=A.IdLoc AND iTransRow.IdSeq=A.IdSeq)

TO '\\transfer\itransrow.dat'

These are fairly large tables, and I need to rebuild this asap 😔

Thanks for any ideas,

Edgard

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jun 06, 2016 at 01:44 AM

    Hello Edgard,

    I think that you can write the query as follows.

    UNLOAD SELECT R.* FROM iTransRow AS R

                      JOIN iTrans    AS A

                      ON R.IdLoc = A.IdLoc AND R.IdSeq = A.IdSeq

                      WHERE A.TransDate >= '2014-01-01'

    TO '\\transfer\itransrow.dat'

    Unfortunately the schema of your table isn't clear.

    So I can't have confidence in my guess.

    Regards,

    Koichi

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 06, 2016 at 02:46 PM

    It might be interesting to see the query plans. Is there an index over the iTransRows FK columns or one with them as prefix? My guess is that the plan of Edgard's correlated subquery and Koichi's join aren't very different. Most likely, the SQ will create a semi- join, which is potentially better, but doesn't make a difference to a PK join.

    What would you expect from a "good" plan: I'd expect that the engine first (outer loop) retrieves all iTrans PK values representing rows more recent then 2014-01-01 and, out of these values, retrieves all iTransRow rows using the PK. If, however, the share of the matching rows is quite high and / or there is no qualifying FK index in iTransRow, the brute force approach might even be the best, i.e. scan iTransRow sequentially and try the referenced iTrans PK for each row to check if the row is qualified.

    One thing you might try is to materialize the matching PK values from iTrans in a temp table and use this materialization in the subquery or join.

    HTH

    Volker

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 10, 2016 at 05:38 AM

    Hi Edgard,

    If just extract only data from the table iTransRow, I think your method is perfect way, maybe you can alter it a little bit like this

    UNLOAD SELECT * FROM iTransRow

    WHERE

    EXISTS (SELECT 1 FROM iTrans AS A WHERE A.Fecha >= '2014-01-01' AND iTransRow.IdLoc=A.IdLoc AND iTransRow.IdSeq=A.IdSeq)

    TO '\\transfer\itransrow.dat'


    If the corrupt table contains other index, you should try using other index to salvage data

    select * from iTransRow with(index ("other_index"))


    Best regards

    Add comment
    10|10000 characters needed characters exceeded