DB2 Import:

==========
General Tips:

  • sql file can be run on DB2 using : db2 -tvf sqlfilename
  • If you also want output of sql in some log file : db2 -tvf sqlfilename | tee -a logfilename
  • After running a sql file, check for error by searching word SQLSTATE in log file.

Prerequisite:
Login to db2 server as instance owner and create database and blank schema.

  • Use Emptoris Suite script to create database (Optional)
  • se Emptoris Suite script to create schema using option 1 for any schema. Option 1 is for VSM, but we recommend it for ECM / Sourcing / SLM also as it creates blank schema with required tablespace.

Once you have database created and blank schema created, connect to database and run following command by replacing ESIECM_ with your osuser name e.g. ABC_

db2 connect to DBNAME (here don’t use osuser authentication, we will use it for creating objects only)

—> :
CREATE ROLE ESIECM_ADMIN_ROLE;
GRANT CREATETAB ON DATABASE TO ROLE ESIECM_ADMIN_ROLE;
GRANT CONNECT ON DATABASE TO ROLE ESIECM_ADMIN_ROLE;
GRANT IMPLICIT_SCHEMA ON DATABASE TO ROLE ESIECM_ADMIN_ROLE;
GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO ROLE ESIECM_ADMIN_ROLE;

Steps to Import:
You need to add –#SET TERMINATOR @ in all sql file in beginning.
ddl1a.sql
—> Remove extra space after schema name e.g. "ESIECM " should be changed to "ESIECM"
Connect to DB using the owner: db2 connect to DBNAME user osusername using osuserpassword
ddll1b.sql

—> Comment out step that creates schema. We are doing that as part of prerequisite.
—> Remove extra space after schema name e.g. "ESIECM " should be changed to "ESIECM"
—>Comment out SET NLS_STRING_UNITS
—> (optional if you are using same tablespace) Remove IN "ESIECM"
—> This file may give failed generate error for some sequences, if it is for creating sequence error in log file, you can ignore it. We will create them in next step.

For ECM Only

—> Errors because Global variable e.g. “ECM_USERID_GV” is not created.
SQL20480W The newly defined object "ESIECM.SYS_CONTEXT" is marked as invalid because it references an object "ECM_USERID_GV" which is not defined or is invalid, or the definer does not have privilege to access it. LINE NUMBER=12. SQLSTATE=0168Y

For ECM Only

Create it manually before running ddl1b.sql:
CREATE VARIABLE ECM_USERID_GV VARCHAR2(32);
CREATE VARIABLE ECM_ACLID_GV VARCHAR2(32);
CREATE VARIABLE ECM_LOGIN_GV VARCHAR2(32);

For ECM Only

—> Create table failed because it have IN "USERTMPTSP1” in defition.
CREATE GLOBAL TEMPORARY TABLE "ESIECM"."SYSCONTEXT_TMP" ( "USER_ID" VARCHAR(128 OCTETS) , "CONTEXT" VARCHAR(128 OCTETS) ) IN "USERTMPTSP1" ON COMMIT PRESERVE ROWS NOT LOGGED ON ROLLBACK DELETE ROWS

For ECM Only

CREATE GLOBAL TEMPORARY TABLE "ESIECM"."SYSCONTEXT_TMP" ( "USER_ID" VARCHAR(128 OCTETS) , "CONTEXT" VARCHAR(128 OCTETS) ) ON COMMIT PRESERVE ROWS NOT LOGGED ON ROLLBACK DELETE ROWS
A table space could not be found with a page size of at least "4096" that authorization ID "ESIECM" is authorized to use.. SQLCODE=-286, SQLSTATE=42727, DRIVER=4.17.30

For ECM Only

—> SQL20480W The newly defined object "ESIECM.DELETE_DATA_DEF_PRO" is marked as invalid because it references an object "LOG_DATA" which is not defined or is invalid, or the definer does not have privilege to access it. SQLSTATE=0168Y

Connect to DB using the owner:db2 connect to DBNAME user osusername using osuserpassword
sequence.sql
—>Run sequence sql file

db2 connect to DBNAME (here don’t use osuser authentication, we will use it for creating objects only)
ddl3.sql
—> This have number of statement that is not required and it bounds to fail.

  1. First replace TO USER "ESIECM " to TO USER esiecm
  2. Then replace "ESIECM " to "ESIECM"

—> It has ~2000 lines , but only useful one is ~700.
[db2inst1@db2 ECM]$ cat ESIECM.ddl3.sql | wc -l
2109
[db2inst1@db2 ECM]$ cat ESIECM.ddl3.sql | grep ESIECM > new_ESIECM.ddl3.sql
db2inst1@db2 ECM]$ cat new_ESIECM.ddl3.sql | wc -l
709

For ECM Only (You can ignore error for package with P as name e.g.P1027968629)

GRANT CONTROL ON PACKAGE "ESIECM"."P1027968629" TO USER esiecm
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0204N "ESIECM.P1027968629" is an undefined name. SQLSTATE=42704

Loading the data
After DDL is run successfully, next is importing data. Put the DBNAME where you created schema and want to import the data.
–Make sure that db2move.lst file have correct schema name
db2move DBNAME LOAD | tee -a load.log

Post Import
Run, below command for the schema.

SELECT ',' || rtrim(tabschema) || '.' || rtrim(tabname) FROM SYSCAT.TABLES where tabschema = '$TGTSCHEMA' and status <> 'N';

–$TGTSCHEMA is the schema you just imported, for example schema name is MYSCHEMA

For example, it gives following output:

-->
,MYSCHEMA.TABLE1
,MYSCHEMA.TABLE2
,MYSCHEMA.TABLE3
<--

Remove ',' from the first record and make the command like this:

SET INTEGRITY FOR
MYSCHEMA.TABLE1
,MYSCHEMA.TABLE2
,MYSCHEMA.TABLE3
IMMEDIATE CHECKED;

You need to run the query using CLP/Data Studio/SQL Script.

====================

1 comment on"How to import DB2 dump for Emptoris Products"

  1. Nice article help me to import DB@2

Join The Discussion

Your email address will not be published. Required fields are marked *