TRANSACTIONSIZE and TRANSFORMATIONMODEL

[federico_rossi]

hello expert

my concern is first to view the actual value of parameter TRANSACTIONSIZE and, second, to change it if necessary

using loadercli I executed as db administrator the statement "dataextract * from TRANSFORMATIONMODEL outfile <my_file>" but unfortunately the file created is empty...

how can that be possible? is there another way to see the value of parameter TRANSACTIONSIZE?

second question: the command SET TRANSACTION SIZE <value> can be executed inside loadercli?

thanks in advance for your help

Edited by: Federico Rossi on Sep 24, 2009 5:59 PM

[lbreddemann]

Now change the parameter ...

C:\Dokumente und Einstellungen\Lars>loadercli -d db77 -u mona,red
Loader protocol: 'C:\Dokumente und Einstellungen\Lars\Eigene Dateien\sdb\loader\log\loader.log'
Loader packages: 'C:\Dokumente und Einstellungen\Lars\Eigene Dateien\sdb\loader\packages'

User MONA connected to database DB77 schema MONA on local host.
loadercli>use schema hotel
OK

---
loadercli>set transaction size 10000
OK

---
loadercli>export schema hotel catalog outstream 'hotel.cat' data outstream 'hotel.data'
OK
---
Total number of tables (definition) exported: 8
Total number of tables (data)       exported: 8 (excluded: 0, failed: 0)

---

And check the table again:

PACKAGEGUID                                       USERNAME  TRANSACTIONSIZE  PART    STEPID  OWNER  TABLENAME      TABLETYPE
080000008009CD00100D0000D4020000AABE7AC473A0ACCD  MONA      10000            SCHEMA  1       MONA   F_STAYS        TABLE
080000008009CD00100D0000D4020000AABE7AC473A0ACCD  MONA      10000            SCHEMA  2       MONA   PERSON         TABLE
080000008009CD00100D0000D4020000AABE7AC473A0ACCD  MONA      10000            SCHEMA  3       MONA   CITY           TABLE
080000008009CD00100D0000D4020000AABE7AC473A0ACCD  MONA      10000            SCHEMA  4       MONA   CITY           TABLE
080000008009CD00100D0000D4020000AABE7AC473A0ACCD  MONA      10000            SCHEMA  5       MONA   

... data cropped ...
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  1       MONA   F_STAYS        TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  2       MONA   PERSON         TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  3       MONA   CITY           TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  4       MONA   CITY           TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  5       MONA    ...  data cropped ...

You see: both exports are separately listed, each with its own set of parameters.

All that is of course documented... see [Loader|http://maxdb.sap.com/doc/7_7/e1/18a4ab1475462f8f4ab9f3bc4a7517/content.htm]

regards,

Lars

[federico_rossi]

Hello Lars,

thank you very much for your detailed post.

I tried to follow your instructions but when I run the command "select packageguid, username, transactionsize, part, stepid, owner, tablename, tabletype from sysloader.transformationmodel" I got the following error:

ERR -25010

SQL error -4004 = Unknown table name:TRANSFORMATIONMODEL (error position: 95[line: 1, col: 95])

By the way, I am interested in evaluating and, if possible, changing the value of parameter TRANSACTIONSIZE, in order to improve the poor performance during the backup of my maxdb database.

BR,

Federico

[Former Member]

Hello,

I do not recommend Loader for database backup.

Loader was designed as an ETL tool and not as a backup tool. e.g.: Loader does not guarantee consistency between tables.

You should use the Back/Recovery from MaxDB.

[original link is broken]

If you want to continue with the Loader you could check the data format. Data format CSV is not very performant.

Best Regards

Wolfgang

[lbreddemann]

BACKUP???

You are kidding us, are you?

Of course Wolfgang is completely right here.

An export is in no way a backup!

So, if this is just about taking backups - you're doing it totally wrong.

regards,

Lars

[lbreddemann]

Let's see an example...

Do an export:

C:\Dokumente und Einstellungen\Lars>loadercli -d db77 -u mona,red
Loader protocol: 'C:\Dokumente und Einstellungen\Lars\Eigene Dateien\sdb\loader\log\loader.log'
Loader packages: 'C:\Dokumente und Einstellungen\Lars\Eigene Dateien\sdb\loader\packages'

User MONA connected to database DB77 schema MONA on local host.
loadercli>use schema hotel
OK

---
loadercli>set transaction size 100
OK

---
loadercli>export schema hotel catalog outstream 'hotel.cat' data outstream 'hotel.data'
OK
---
Total number of tables (definition) exported: 8
Total number of tables (data)       exported: 8 (excluded: 0, failed: 0)

---
loadercli>exit
OK

Check the transformationmodel table...

select packageguid, username, transactionsize, part, stepid, owner, tablename, tabletype from sysloader.transformationmodel

PACKAGEGUID                                       USERNAME  TRANSACTIONSIZE  PART    STEPID  OWNER  TABLENAME      TABLETYPE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  1       MONA   F_STAYS        TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  2       MONA   PERSON         TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  3       MONA   CITY           TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  4       MONA   CITY           TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  5       MONA   CUSTOMER       TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  6       MONA   HOTEL          TABLE
0B0000001008CD00900B0000E32B00008F71FFF2EAE6205A  MONA      100              SCHEMA  7       MONA   ROOM           TABLE
... needed to crop data due to forum limitation...

---> continue in next post

[lbreddemann]

> my concern is first to view the actual value of parameter TRANSACTIONSIZE and, second, to change it if necessary

>

> using loadercli I executed as db administrator the statement "dataextract * from TRANSFORMATIONMODEL outfile <my_file>" but unfortunately the file created is empty...

> how can that be possible? is there another way to see the value of parameter TRANSACTIONSIZE?

Well, I would guess that you either did never run an export/import (that's what is called TRANSFORMATION in the loader-speak) or the table had been deleted meanwhile.

Anyhow, usually you would use tools like SQL Studio, DB Studio or sqlcli to review data of tables...

> second question: the command SET TRANSACTION SIZE <value> can be executed inside loadercli?

Yes, of course.

You can put it into the loader script file you use for the export.

If you're using loadercli in the interactive transport mode (you know, that thing with the text-menu...) then you cannot change this (or any other) parameter.

It's either simple or a bit complex...

What I'm wondering about is: why do you want to change this parameter?

Do you believe increasing it makes the export faster? If so - what's the rationale behind this?

It just defines after how many processed rows a commit should be done... do you see any problems with large transactions on your system during the export/import?

--> example see next post