USEFUL TOOLS IN DB2 LUW
In this post I want to discuss about some of tools available with DB2 DB with reduces the effort of DBA.
Data Movement Tools
Data movement is a very frequent operation in any environment, let it be for loading data into lower env or be it for migrating tables into higher page size.There are several ways to conduct the movement and Export, Import, Load, db2move, db2relocatedb, db2look are some of the data movement tools available with DB2. Depending on the purpose and scenario we get to choose the method. We are going to see the usage of these tools in today's post. Starting with Export and Import are used to pull data out of table into file (.csv and other formats) and load data into table from a file ( of .csv, txt and other formats) respectively.
db2 Export
Using db2 export we can export data into a given file of format and it provides users with option to send messages to a separate file, we can even redirect Lobs and XML columns to other files. We can retrieve data from table using select statement.
db2 export to <File_Name>.ext of ext messages <msg_file>.ext <Select Statement>
ex:
db2 export to sample.del of del messages msgs.txt select * from emp where dept = 20
Using db2 Import we can load data from a file into table. This tool provides multiple options for loading data into a table which includes option to specify column order/column names and also the commit count. Below is a sample use of import
db2 import from samplefile.del of del messages msg.txt insert into staffdb2move
Db2move is a tool used to make bulk movement in most occasions. DB2move allows user to performs actions like export, import load and copy with multiple tables at once or the whole database. The action specified should be only one of those mentioned. It allows movement of entire schema as well into another DB but the target Db should be a local DB. Below is the syntax for db2move
db2move--dbname--action----+------------------------+-+---
+- -tc--table-definers---+
+- -tn--table-names------+
+- -sn--schema-names-----+
+- -ts--tablespace-names-+
+- -tf--filename---------+
+- -io--import-option----+
+- -lo--load-option------+
+- -co--copy-option------+
+- -l--lobpaths----------+
+- -u--userid------------+
+- -p--password----------+
'- -aw-------------------'
db2look
It is not exactly a data movement tool rather it is a data defining tool. It provides complete DDL, DCL and statistical information in a executable format. Below is an example for db2look statement.
db2look -d <database_name> -u <creator> -z <schema_name> -e -o <output_filename>
No comments:
Post a Comment