CODEWITHSUNDEEP
sqldb2dump
jrharshath
jrharshath

Reputation: 26583

SQL Dump from DB2

I'm trying to dump the contents of a particular schema in one IBM DB2 UDB server into an sql text file (much like the mysqldump functionality of mysql).

I came across db2look, but it only dumps the structure of the schema (only ddl, no dml).

So how can I get my thing done?

jrh.

Upvotes: 10

Views: 22779

Answers (5)

Tam
Tam

Reputation: 3987

Db2 Schema with all DDL Backup :

I have use below command it worked for me to export all DDL. 

db2look -d CusDb -x -e -z CusSchema -o OutputFile

Syntax : db2look -d DbName -x -e -z SchemaName -o OutputFile_name

Upvotes: 1

Michael Sharek
Michael Sharek

Reputation: 5069

What you're looking for is the db2move command. For a particular schema you should use the "sn" switch.

So for example to export the data:

db2move [your_db_name] EXPORT -sn [your_schema_name]

There are many options and switches available for db2move depending on exactly what you want to do.

If db2move is not exactly what you need, you can review the table of Data Movement Options available in DB2.

Upvotes: 10

Tisme
Tisme

Reputation: 1

For importation, adjusting slightly to use the load helps avoid rows being rejected.

db2 -x "SELECT 'load FROM /usr/data/SCHEMA/' || TABNAME || '.ixf OF IXF LOBS FROM /usr/data/SCHEMA/ MODIFIED BY identityoverride INSERT INTO CFEXT.' || TABNAME || ';'FROM SYSCAT.TABLES WHERE TABSCHEMA = 'CFEXT' ORDER BY TABNAME" > /tmp/db2cfeimport.sql

Upvotes: 0

Ryan Emerle
Ryan Emerle

Reputation: 15811

You could use the EXPORT, and related IMPORT or LOAD commands if the goal is to transfer data back into another DB2 database.

In fact, you can generate the the statements based on metadata from SYSCAT.TABLES

EXPORT

   SELECT 'EXPORT TO /usr/data/SCHEMA/' || TABNAME || '.ixf OF IXF LOBS TO /usr/data/SCHEMA/lbos/ MODIFIED BY LOBSINFILE SELECT * FROM SCHEMA.' || TABNAME || ';'
     FROM SYSCAT.TABLES
    WHERE TABSCHEMA = 'SCHEMA'
 ORDER BY TABNAME

IMPORT

   SELECT 'IMPORT FROM /usr/data/SCHEMA/' || TABNAME || '.ixf OF IXF LOBS FROM /usr/data/SCHEMA/lobs/ MODIFIED BY LOBSINFILE INSERT INTO SCHEMA.' || TABNAME || ';'
     FROM SYSCAT.TABLES
    WHERE TABSCHEMA = 'SCHEMA'
 ORDER BY TABNAME

If you want the actual insert scripts, then you may need to go with a third-party tool (I'm not aware of one provided by DB2, though I could be wrong.)

Upvotes: 2

rudolfson
rudolfson

Reputation: 4146

You could use SQquirreL, an SQL Client implemented in Java, to accomplish this. In its "Objects"-Tree you would select all desired tables and select "Scripts > Create Data Script" from the context menu.

Upvotes: 5

Related Questions