Archives of the TeradataForum

Message Posted: Thu, 20 Nov 2003 @ 16:05:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

There are numerous ways to do this.

1.) Use Archive/Restore to move just the dictionary entries for the databases over to the test system.

Syntax would be something like.

     To archive:
        archive dictionary tables (database1) all, (database2) all,
        release lock,
        .file = somefile you want;

     To restore
        copy dictionary tables (database1), (database2) ,
        release lock
        .file = the file you used in the archive;

You might need to run the following statement after the copy

(I believe this option is only if you do cluster archives or need to rebuild SI on the tables.)

2.) Something I use quite often although it sometimes requires some manual manipulation (from unix)

        First issue the following sql from a bteq script
        .logon x/user,password;
        .set titledashes off;
        .width 225
        .export report file = ./somefile

        select 'show table ' || trim(databasename) || '.' || trim(tablename) || ';'
        from dbc.tables
        where tablekind = 't'
        order by databasename, tablename;

Once that script returns then edit the file that it creates open the file 'somefile' in vi

add the following lines

        .logon x/user,password;
        .set titledashes off;
        .width 225
        .export report file = ./tablecreate

        /*** start of the file generated by sql ****/
        show table xxxxx.xxxxx;
        .....

save the changes then run that script through bteq to the table create statements from Teradata.

this will return all of the current table schemas that Teradata knows of.

        After this script is finished.
        edit the file 'tablecreate' in vi
        add the following lines

        .logon y/user, password;

        create table xxxx.xxxxx as .......

This will then run the create table statements on the new system.

I use this when i need to replicate my production environment on test in a hurry. I can generally lay the entire set of tables down in about 15min. Of course I have this whole process more or less automated. I just put down the main points here.

3.) Use Winddi to copy stuff - Never use this option cause I don't trust gui interfaces when it comes to database manipulation.

Those are the only options I can think of off the top of my head. To be honest, the 2nd option I find to be the most flexible and the least hassle to do. Although sometimes i have run into issues where the tables don't seem to be formatted correctly when the show table is returned.

Bill Grenwelge
Data Warehouse
Union Planters Bank

	<Prev	Next>		<<First	<Prev	Next>	Last>>