Dynamic Table Dump

[stokefc22]

Progress version 10.1C

Hi All,

I'm trying to dump a number of tables(100+) to a .d file. I know I can do this as follows and cant complain it works fine..

define variable chrDumpFile as character    no-undo.                          

define stream dumpStream.

assign chrDumpFile = "c:\temp\deckleTest\pricelist.d":u.

output stream dumpStream to value(chrDumpFile).

for each abbey.pricelist no-lock:

    export stream dumpStream abbey.pricelist.
end.

output stream dumpStream close.

However what I really want to do is create a procedure to do this dynamically I really don't want to have to type out the above over 100 times!!

The closest I have got is below..

    define input  parameter ipHanTable  as handle      no-undo.
    define input  parameter ipHanStream as handle      no-undo.

    output /*stream-handle ipHanStream*/ to value(substitute("c:\temp\deckleTest\&1.d",ipHanTable:name)).

    define variable hanQuery        as handle      no-undo.
    define variable intCount        as integer     no-undo.
    define variable chrStreamData   as character   no-undo.
    define variable intExtentCount  as integer     no-undo.

    create query hanQuery.

    hanQuery:set-buffers(ipHanTable).
   
    hanQuery:query-prepare(substitute("for each &1.&2":u,ipHanTable:dbname,ipHanTable:name)).

    hanQuery:query-open().

    do while hanQuery:get-next():
        assign chrStreamData = "":u.

        do intCount = 1 to ipHanTable:num-fields:
            if ipHanTable:buffer-field(intCount):extent > 0 then
            do:              
                do intExtentCount = 1 to ipHanTable:buffer-field(intCount):extent:
                    if chrStreamData <> "":u then assign chrStreamData = chrStreamData + " ":u.

                    if ipHanTable:buffer-field(intCount):data-type = "character":u then                   
                        assign chrStreamData = chrStreamData + quoter(ipHanTable:buffer-field(intCount):buffer-value[intExtentCount]).
                    else
                        assign chrStreamData = chrStreamData + ipHanTable:buffer-field(intCount):buffer-value[intExtentCount].

                    /*export stream-handle ipHanStream ipHanTable:buffer-field(intCount):buffer-value[intExtentCount].*/
                end.
            end.
            else
            do:
                if chrStreamData <> "":u then assign chrStreamData = chrStreamData + " ":u.

                if ipHanTable:buffer-field(intCount):data-type = "character":u then
                    assign chrStreamData = chrStreamData + quoter(ipHanTable:buffer-field(intCount):buffer-value).
                else
                    assign chrStreamData = chrStreamData + ipHanTable:buffer-field(intCount):buffer-value.

                /*export stream-handle ipHanStream ipHanTable:buffer-field(intCount):buffer-value.*/
            end.              
        end.     

        put unformatted chrStreamData skip.
/*         export stream-handle ipHanStream chrStreamData. */
    end.

    output /*stream-handle ipHanStream*/ close.

This works but is upto 4 or 5 times slower than the hardcoded method and with some of the tables being rather large this is quite an overhead.

Does anyone have an alternative to the line below for a dynamic query?

    export stream dumpStream abbey.pricelist.

thanks in advance!

 

[Rob Fitzpatrick]

proutil dbname -C dump tablename /directory

It's pretty straightforward to dump out a shell script or batch file with one proutil dump line per table. It will dump your data a lot faster than ABL export; that's probably the slowest way to do it when your tables are large.

 

[Rob Fitzpatrick]

And if you really need .d files instead of .bd, you can use the data dictionary. Its code is available, should you want to do this programmatically. Look at dump_d.p in the prodict source; you may have to extract it first.

 

[stokefc22]

thanks Rob I shall have a look

 

[GregTomkins]

I'm interested in the claim that dynamic anything is 4-5x slower than static. I benchmarked this (not with export though) in the past and found a < 5% difference. That was many versions ago, though, plus, I probably didn't do it super-carefully. I'm wondering if RHD has any knowledge of this?

 

[TheMadDBA]

I would imagine the extra overhead is coming from dealing with each field individually (and appending to a character variable) instead of just exporting the entire table... now if we could finally get a dynamic buffer export that isn't JSON or XML that would be great.

Another alternative is just to generate the 100+ sections of code using the _File table.

 

[Rob Fitzpatrick]

What is the end goal? What are you doing with the .d files once they have been dumped?

 

[RealHeavyDude]

As a side note: I don't see any clean up code that destroys the query object. If it is not part of the code that you did not disclose you are producing a memory leak and your ABL session will get slower and slower and slower ...

Heavy Regards, RealHeavyDude.

 

[Cringer]

As a side note: I don't see any clean up code that destroys the query object. If it is not part of the code that you did not disclose you are producing a memory leak and your ABL session will get slower and slower and slower ...

Heavy Regards, RealHeavyDude.

Well spotted!

 

[stokefc22]

Apologies Greg.. seems my maths was terrible! The first method took 1 minute 39 seconds and the second method something like 4 minutes 50. This is probably our largest table but still it is quite a difference and when you start to dump a large number of tables it soon racks up...

I did have a version that was creating a dynamic tt and then sticking that to xml but that took even longer

The end goal is a program that the user can use on a live DB to dump out a load of data and then switch to the test DB to import this data to allow them to run various tests of differing scenarios without having to effect the live system.

You are indeed right RHD.. I'm usually a stickler for this but I was just knocking it up in an editor and did miss it... wrist slapped

I've settled on a solution that seems to take the same amount of time as the static version but allows me to reduce the amount of code I need to write... I don't like it but it works. Basically I have an include that does all the work and just pass the table name through. It was turning into a can of worms for something that is probably not going to be utilised all that much anyhow... how soul destroying hey!

 

[RealHeavyDude]

You must be aware that the include file solution will be resolved at compile time - NOT at runtime. Therefore the users need to have either a developer license installed on their machine or you need to deploy it as encrypted source ( which can be executed with just a runtime license installed ).

Heavy Regards, RealHeavyDude.

 

[stokefc22]

Yeah cheers RHD, we only ever deploy compiled code anyway so that's no problem.

 

[Rob Fitzpatrick]

I'm trying to dump a number of tables(100+) to a .d file.

The end goal is a program that the user can use on a live DB to dump out a load of data and then switch to the test DB to import this data to allow them to run various tests of differing scenarios without having to effect the live system.

I don't think any method of dumping over 100 tables from production can be said to run without affecting prod. That said, the approach that has the least impact will be the one that is most efficient. Since your only requirement is to dump entire tables and you don't need to transform the data as you dump it, I recommend using binary dump.

Binary dump is faster for large tables than FOR EACH... EXPORT, your table list can be chosen at runtime so you can still use a dynamic approach, and you can even dump the tables in parallel if desired, which you cannot do with the single-threaded EXPORT approach.

 

[TheMadDBA]

This sounds like a job for AI based replication.... you are running AI already right?

 

[stokefc22]

Thanks guys I'll look into the binary dump, as for the AI yes we do have this running but there is the limit of my knowledge I'm afraid.

 

[Cringer]

With OE Replication (which uses the AIs) you can replicate your database to a different database. It's not that tricky to set up, and would only give you a read only version of the live database though so it wouldn't suit what I think you are trying to do.
Wouldn't it just be quicker to apply your latest backup to a testing DB so that it's in line with live, rather than faffing about with specific tables?

 

[stokefc22]

Your probably right cringer!

I may well be mistaken though but wouldn't that involve me having to do that for them every time they want to refresh it?

Faffing is certainly the right word, its a pain for something that as I said further up will probably end up never being used!

 

[Cringer]

Yeah you'd have to apply a backup each time they want it fresh, but it may well be worth testing how long it takes to apply the backup. If you use a script and have the backups somewhere accessible to the test machine then it should just be a case of run the script and wait. If it's quicker, or similar speed then it saves you a lot of hassle.

 

[TheMadDBA]

Either way you are going to have to do something every time they wanted it refreshed. The benefits of using AI to replicate or restoring backups are...

1) It is a real copy of the DB with no margin for error introduced with new tables/columns/etc
2) You actually test your backups/AI on a regular basis

You don't have to use OE Replication, there are a number of ways to handle this situation. If you are doing nightly online backups the restore to dev can be scripted pretty easily.