Script to fill the database for a tutorial

[christian_jansen]

Hi,

I'm looking for a script to fill either the data volumes or the log volumes of a MaxDB 7.6.00.35 with sample data.

I tried with x_python filldb.py but I got the error <i>SQLError: sql.SQLError: [-4004] (at 30) Unknown table name: INFO_SYSDD_SERVERDBS</i>.

Is there any new script available or does anybody know how to rig the script filldb.py ?

Any comment welcome !

Thanx and regards,

Christian

[lbreddemann · Accepted solution]

Hi Christian,

I'm not sure where you got the filldb.py script, but to generate some log you don't need to use it.

Just do something like this:

create table logger (col1 char(200))

-> now turn off autocommit in SQL Studio

insert into logger select lpad('x',200) from tables t1, tables t2, tables t3 where rownum <=1000

//

rollback

//

execute this statement a few times your log area should get filled quickly.

You can also set 10000 or 100000 rows at a time, but when a LOGFULL occurs you'll have just the option to make a log backup.

KR Lars

[christian_jansen · Accepted solution]

Lars,

thanx for your answer. It seems to be a little error in your script. When I execute it in SQL studio it tells me the following.

<i><b>---- Error -

---

</b>

Auto Commit: Off, SQL Mode: Internal, Isolation Level: Committed

General error;-5016 POS(39) Missing delimiter: ,

insert into logger select lpad('x',200) from tables t1, tables t2, tables t3 where rownum <=1000</i>

As I have no idea where the error is (I'm not a programmer) I played around a little bit with the lpad string. I tried the following:

<i>insert into logger select lpad('x',2,'y',4) from tables t1, tables t2, tables t3 where rownum <=1000

//

rollback

//</i>

- but then the following error occured:

<i><b>---- Error -

---

</b>

Auto Commit: Off, SQL Mode: Internal, Isolation Level: Committed

Column not found;-4005 POS(88) Unknown column name:ROWNUM

insert into logger select lpad('x',2,'y',4) from tables t1, tables t2, tables t3 where rownum <=1000</i>

Then I deleted the "where"-clause:

<i>insert into logger select lpad('x',2,'y',4) from tables t1, tables t2, tables t3

//

rollback

//</i>

- with the following result:

<i><b>---- Error -

---

</b>

Auto Commit: Off, SQL Mode: Internal, Isolation Level: Committed

General error;-904 POS(1) Space for result tables exhausted

insert into logger select lpad('x',2,'y',4) from tables t1, tables t2, tables t3</i>

Any clues ?

Thanx a lot!

Christian

[lbreddemann · Accepted solution]

Hi Christian,

I ran the statements in ORACLE mode - so just test this.

When you delete the WHERE clause it's no wonder you get space problems.

I designed the query in a way that a huge number of rather big rows is returned.

Look at it: there is a Cartesian Product of three tables !

And I select a string that has 200 bytes.

Since for the INSERT INTO SELECT... the resultset of the select is first put into a temporary table in the database.

Therefore we need 200 bytes x (no. of entries in tables)^3 to store the data.

As you wrote all you wanted was to create REDO - so just leave the WHERE condition in place (also ROWNUM does not work in mode INTERNAL, it's named ROWNO there - so just use ORACLE mode for the sake of this example).

The trick here is that although much data is manipulated it's always rolled back, so the permanent space usage in the database will not increase.

Hope that helps.

KR Lars

[Former Member]

Christian,

This python script sounds like it was one of the ones that came with the tutorial for one of the earlier versions of SAP DB (I seem to remember using one of this for a 7.3 setup). You might be able to poke around by editing the script but it might be just as easy to use the new DB management studio and create your own loads using the import function.

Hope that helps.

Thanks,

J. Haynes

Denver, CO

[christian_jansen]

Joe,

thanx for your answer. You wrote :"<i>it might be just as easy to use the new DB management studio and create your own loads using the import function.</i>".

That's my problem: How to create an "own load" - even with DBStudio ? As I'm not a programmer I have no idea how to create this. Maybe you can provide me with more info or a docu.

Thanx a lot!

Christian

[former_member229109]

Hello Christian,

-> The "Loader Tutorial" & "SQL Tutorial" are available at

http://maxdb.sap.com/currentdoc/default.htm -> Tutorials

< Loader Tutorial -> "Demo Databases DEMODB and TESTDB"

"You can load all demo data into the database instance DEMODB at a later stage if you do not load all data when you create the database instance. You can use the Database Manager tGUI or CLI o load all demo data." >

Thank you and best regards, Natalia Khlopina