Solved

How to restore a db2 database backuped in windows into linux

Posted on 2006-11-13
97
4,214 Views
Last Modified: 2008-01-09
Hi gurus,

I backuped a db2 database in windows, now I need to restore it in linux.

Could some expert here tell the steps for restore a db2 database in linux?

Thank you so much for your reply,

lilyyan

0
Comment
Question by:lilyyan
  • 65
  • 29
  • 3
97 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 17932178
Hi Lilyyan,

My understanding is that the backup files do not port between different host environments.  That is, a Windows backup can be restored on another Windows instance, but on on Unix, linux, Z/OS, etc.

If the database is of modest size, you might export the data to a Windows file, then import it into linux.

If the database is quite large, consider building a federated environment.  Then you can build the database copy onto the linux box from a single client that is connected to both environments.


Good Luck,
Kent
0
 

Author Comment

by:lilyyan
ID: 17932366
Hi Kent,

Thanks so much for your reply.

>If the database is of modest size, you might export the data to a Windows file, then import it into linux.

how to do this step?

lilyyan
0
 
LVL 45

Expert Comment

by:Kdo
ID: 17932409

You'll use the db2 command line tool.  The DB2 documentation give a better description that I can, here's the URL:


   http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0008303.htm



Kent
0
 

Author Comment

by:lilyyan
ID: 17932460
may i just go ahead try if the backup file works in linux?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17932483
hi

you can try to use the backup file in linux,
in the worse case, if it won't work, you will have to copy the database some other way...
that other way will be
use db2look to extract the ddl from the source envrionment
use db2move to extract the data from the source envrionment

then use the ouput of the above 2 and load the database in the target envrionment
the output of those utilities is 100% portable between platforms
i used it 2 hours ago to port from linux to windows.. :-)

momi
0
 

Author Comment

by:lilyyan
ID: 17932516
hello,

thanks so much for all replies. may you specify the steps/command for restore a db in linux?

currrently, i only have the backup file in linux

thank you so much,

lilyyan

0
 

Author Comment

by:lilyyan
ID: 17932699
hi momi,

you just restore a db right, could you please explain in detail how to use these command?

use db2look to extract the ddl from the source envrionment
use db2move to extract the data from the source envrionment

then use the ouput of the above 2 and load the database in the target envrionment
0
 

Author Comment

by:lilyyan
ID: 17932714
from windows to linux
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17932732
hi

you can not copy / restore on different platforms
you can read that here

http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/core/r0001933.htm

so i guess that you will need to use the db2look and db2move approach

momi
0
 

Author Comment

by:lilyyan
ID: 17932817
how to use db2look and db2move approach from windows to linux? may you explain in detail. i'm new to db2.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 17932864
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 17932894
hi

you can try something like this, from the command line (windows prompt)
go to a dedicated directory (becuase db2move creates many output files)

db2look -d <db_name> -e  -o OutputFileName
db2move <db_name> export

then copy all these files to the linux machine
on the linux machine, create a database with the same name
on the linux machine open a terminal window with the instance user name and perform

db2 < OutputFileName
db2 <db_name> import

that should do all the job for you

momi
0
 

Author Comment

by:lilyyan
ID: 17933235
hi,

thanks very much for your reply. i'm trying move the db. i will get back this question about  
1 hour later
0
 

Author Comment

by:lilyyan
ID: 17933302
should the OutputFileName file name has any file extension?
0
 

Author Comment

by:lilyyan
ID: 17933853

in this step: >then copy all these files to the linux machine

should the file be put in a particular directory in linux?
0
 

Author Comment

by:lilyyan
ID: 17933971
ok i already copied the files into linux

> on the linux machine, create a database with the same name

1. in linux, i typed db2, then i enter the db2 commond prompt: db2=>

how to create a db2 dataabse?

2. or in linux, i should type db2start, then create a database
0
 

Author Comment

by:lilyyan
ID: 17934075
i logged into linux as user db2inst1 , then create the database

>on the linux machine open a terminal window with the instance user name and perform

could you please explain how to oen a terminal window with the instance user name?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17934083
when you are in your linux
if db2 is not started then enter db2start to start it

once it is started create the database into which your load the data
then from the terminal
(not from the db2=> prompt)
db2 < OutputFileName

this will run all the commands that db2look generated as a script
0
 

Author Comment

by:lilyyan
ID: 17934156
after i logged into linux as user1, i had to do su - db2inst1, then i can run the db 2 command.

the current sistuation, i put all the database output file under user1 home directory

user1 does't have permission to run db2 command

how to restore the database output file saved under user1 directory ?

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17934183
can you log on to the linux as root ?

if so, just copy them to db2inst1 directory or give permissions on them to everyone

if not,
from your windows
open ftp session with your linux using db2inst1 user and just send the files to db2inst1's home directory
0
 

Author Comment

by:lilyyan
ID: 17934331
hi,

thanks for your reply. when i execute the command as db2inst1:

db2 < OutputFileName, the result is:

 DB21034E  The command was processed as an SQL statement because it was no                                              t a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
db2 => SQL0104N  An unexpected token "WORK;" was found following "COMMIT".  Expe                                              cted
tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17934374
before you run
db2 < OutputFileName

you need to run
db2 connect to <database-name>
0
 

Author Comment

by:lilyyan
ID: 17934397
cool : )

0
 

Author Comment

by:lilyyan
ID: 17934437
after i run the connect command, the result is:

Database Connection Information

 Database server        = DB2/LINUX 8.1.6
 SQL authorization ID   = DB2INST1
 Local database alias   = CSTMDB

then i run db2 < OutputFileName,

there is still error
0
 

Author Comment

by:lilyyan
ID: 17934444
the error is:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
db2 => DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
0
 

Author Comment

by:lilyyan
ID: 17934498
how do i know CSTMDB is already existed
0
 

Author Comment

by:lilyyan
ID: 17934633
hi momi, would you please have some suggestion?
0
 

Author Comment

by:lilyyan
ID: 17934666
is this :db2 < OutputFileName

a redirected restore operation ? well, it may not work different o.s. system
0
 

Author Comment

by:lilyyan
ID: 17934919
HI,

i also try the approach in this link:

in the last step:

the result is:
------------------------------------------------------------------------------------------------------------------------------------------------------
*****  DB2MOVE  *****

Action:      LOAD

Start time:  Mon Nov 13 19:17:31 2006


Connecting to database OSCMDB ... successful!  Server: DB2 Common Server V8.1.6


***  Table "CSTMEMBERDBSCHEMA"."CSTMEMBERPROFILE":  ERROR -3304.  Check message                                               file tab1.msg!
***  SQLCODE:  -3304  -  SQLSTATE:
***  SQL3304N  The table does not exist.


***  Table "OSCMEMBERDBSCHEMA"."CSTMEMBERKEYWPORD":  ERROR -3304.  Check message                                               file tab2.msg!
***  SQLCODE:  -3304  -  SQLSTATE:
***  SQL3304N  The table does not exist.


***  Table "SYSTOOLS"."ALTOBJ_INFO":  ERROR -3304.  Check message file tab3.msg!
***  SQLCODE:  -3304  -  SQLSTATE:
***  SQL3304N  The table does not exist.


***  Table "SYSTOOLS"."DB2LOOK_INFO":  ERROR -3304.  Check message file tab4.msg                                              !
***  SQLCODE:  -3304  -  SQLSTATE:
***  SQL3304N  The table does not exist.


***  Table "SYSTOOLS"."HMON_ATM_INFO":  ERROR -3304.  Check message file tab5.ms                                              g!
***  SQLCODE:  -3304  -  SQLSTATE:
***  SQL3304N  The table does not exist.


***  Table "SYSTOOLS"."POLICY":  ERROR -3304.  Check message file tab6.msg!
***  SQLCODE:  -3304  -  SQLSTATE:
***  SQL3304N  The table does not exist.


***  Table "SYSTOOLS"."HMON_COLLECTION":  ERROR -3304.  Check message file tab7.                                              msg!
***  SQLCODE:  -3304  -  SQLSTATE:
***  SQL3304N  The table does not exist.


***  Table "SYSTOOLS"."STMG_DBSIZE_INFO":  ERROR -3304.  Check message file tab8                                              .msg!
***  SQLCODE:  -3304  -  SQLSTATE:
***  SQL3304N  The table does not exist.


Disconnecting from database ... successful!
-------------------------------------------------------------------------------------------------------------------------------------------------------------
the message shows all the table are not exist

well, actually i only have two table: CSTMEMBERDBSCHEMA.CSTMEMBERPROFILE and OSCMEMBERDBSCHEMA.CSTMEMBERKEYWPORD

i used db2 cintrol center to create table, i don't know how other tables come from ? . even though, the above message still show the two tables are not exist
0
 

Author Comment

by:lilyyan
ID: 17934922
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17936212
hi

if you are able to connect to the database (and you are since you got the connected prompt) then it is already exist
this operation
db2 < Outputfile
is not a redirect restore
this command just invoke the db2 command line processor and tell it to take the input from the outputfile
it should create all your tables
try to do this instead
edit the outputfile and add a connect to <dbname> command in the begining,
then run this from the terminal window
db2 -f outputfile

after that, run the db2move as you did

momi
0
 

Author Comment

by:lilyyan
ID: 17937927
hi,

thanks very much for the reply. when i open the Outputfile, the command connect to <dbname> is already in the begaining
of Outputfile

and i run db2 -f outputfile, the result is:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003


lilyyan
0
 

Author Comment

by:lilyyan
ID: 17937950
also the error is:

SQL0104N  An unexpected token "RESET;" was found following "CONNECT".
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

--------------------------------------------


the last three line of Outputfile is:

COMMIT WORK;

CONNECT RESET;

TERMINATE;
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17937960
hi

can you please post here the outputfile ?

thanks
0
 

Author Comment

by:lilyyan
ID: 17938021
the file is:

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

-- This CLP file was created using DB2LOOK Version 8.2
-- Timestamp: 11/13/2006 4:38:41 PM
-- Database Name: OSCMDB        
-- Database Manager Version: DB2/NT Version 8.2.0          
-- Database Codepage: 1252
-- Database Collating Sequence is: UNIQUE


CONNECT TO OSCMDB;



---------------------------------
-- for existing granted privileges, excluding the original definer of the object
---------------------------------


CREATE SEQUENCE "SYSTOOLS"."ALTOBJ_SEQ" AS INTEGER
      MINVALUE 1 MAXVALUE 2147483647
      START WITH 1 INCREMENT BY 1
      NO CACHE CYCLE ORDER;
CREATE SEQUENCE "SYSTOOLS"."DB2LOOK_TOKEN" AS INTEGER
      MINVALUE 1 MAXVALUE 20
      START WITH 1 INCREMENT BY 1
      CACHE 20 CYCLE NO ORDER;

------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."HMON_ATM_INFO"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."HMON_ATM_INFO"  (
              "SCHEMA" VARCHAR(128) NOT NULL ,
              "NAME" VARCHAR(128) NOT NULL ,
              "CREATE_TIME" TIMESTAMP NOT NULL ,
              "STATS_FLAG" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "STATS_LOCK" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "STATS_OPTS" INTEGER NOT NULL WITH DEFAULT 0 ,
              "LAST_WAIT" INTEGER NOT NULL WITH DEFAULT 4 ,
              "TO_WAIT" INTEGER NOT NULL WITH DEFAULT 1 ,
              "SIGNAL" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "LEO_TAB" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "EXEC_COUNT" BIGINT NOT NULL WITH DEFAULT 0 ,
              "STATS_STATE" SMALLINT NOT NULL WITH DEFAULT 0 ,
              "STATS_NOTIFY" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "STATS_DETAIL" VARCHAR(200) ,
              "STATS_HISTORY" VARCHAR(200) FOR BIT DATA ,
              "STATS_TIME" TIMESTAMP ,
              "REORG_FLAG" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "REORG_LOCK" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "REORG_STATE" SMALLINT NOT NULL WITH DEFAULT 0 ,
              "REORG_TIME" TIMESTAMP ,
              "REORG_NOTIFY" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "REORG_DETAIL" VARCHAR(200) ,
              "REORG_HISTORY" VARCHAR(200) FOR BIT DATA ,
              "REORG_AVG_RUNTIME" INTEGER )  
             IN "SYSTOOLSPACE" ;


-- DDL Statements for unique constraints on Table "SYSTOOLS"."HMON_ATM_INFO"

ALTER TABLE "SYSTOOLS"."HMON_ATM_INFO"
      ADD CONSTRAINT "ATM_UNIQ" UNIQUE
            ("SCHEMA",
             "NAME");

------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."POLICY"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."POLICY"  (
              "MED" VARCHAR(128) NOT NULL ,
              "DECISION" VARCHAR(128) NOT NULL ,
              "NAME" VARCHAR(128) NOT NULL ,
              "UPDATE_TIME" TIMESTAMP NOT NULL ,
              "POLICY" BLOB(2097152) LOGGED NOT COMPACT )  
             IN "SYSTOOLSPACE" ;


-- DDL Statements for unique constraints on Table "SYSTOOLS"."POLICY"

ALTER TABLE "SYSTOOLS"."POLICY"
      ADD CONSTRAINT "POLICY_UNQ" UNIQUE
            ("MED",
             "DECISION",
             "NAME");

------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."HMON_COLLECTION"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."HMON_COLLECTION"  (
              "HI_ID" BIGINT NOT NULL ,
              "OBJ_NAME1" VARCHAR(128) NOT NULL ,
              "OBJ_NAME2" VARCHAR(128) NOT NULL WITH DEFAULT '' ,
              "OBJ_NAME3" VARCHAR(128) NOT NULL WITH DEFAULT '' ,
              "OBJ_DETAIL" VARCHAR(512) ,
              "OBJ_STATE" INTEGER NOT NULL ,
              "OBJ_REFRESH_TIME" TIMESTAMP NOT NULL ,
              "NOTIFY" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "HISTORY" VARCHAR(200) FOR BIT DATA )  
             IN "SYSTOOLSPACE" ;


-- DDL Statements for unique constraints on Table "SYSTOOLS"."HMON_COLLECTION"

ALTER TABLE "SYSTOOLS"."HMON_COLLECTION"
      ADD CONSTRAINT "HI_OBJ_UNIQ" UNIQUE
            ("HI_ID",
             "OBJ_NAME1",
             "OBJ_NAME2",
             "OBJ_NAME3");

------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."STMG_DBSIZE_INFO"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."STMG_DBSIZE_INFO"  (
              "SNAPSHOT_TIMESTAMP" TIMESTAMP NOT NULL ,
              "DB_SIZE" BIGINT NOT NULL ,
              "DB_CAPACITY" BIGINT NOT NULL )  
             IN "SYSTOOLSPACE" ;


------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."ALTOBJ_INFO"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."ALTOBJ_INFO"  (
              "ALTER_ID" INTEGER NOT NULL ,
              "SQL_OPERATION" VARCHAR(128) NOT NULL ,
              "OBJ_TYPE" VARCHAR(20) NOT NULL ,
              "OBJ_SCHEMA" VARCHAR(128) NOT NULL ,
              "OBJ_NAME" VARCHAR(128) NOT NULL ,
              "CREATION_TIME" TIMESTAMP ,
              "EXEC_MODE" CHAR(8) NOT NULL ,
              "EXEC_SEQ" INTEGER NOT NULL ,
              "SQL_STMT" CLOB(2097152) LOGGED NOT COMPACT NOT NULL ,
              "SQL_CODE" INTEGER ,
              "SQL_STATE" VARCHAR(5) ,
              "SQL_ERRMC" VARCHAR(70) )  
             IN "USERSPACE1" ;


-- DDL Statements for unique constraints on Table "SYSTOOLS"."ALTOBJ_INFO"

ALTER TABLE "SYSTOOLS"."ALTOBJ_INFO"
      ADD UNIQUE
            ("ALTER_ID",
             "EXEC_MODE",
             "EXEC_SEQ");

------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."DB2LOOK_INFO"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."DB2LOOK_INFO"  (
              "OP_TOKEN" INTEGER NOT NULL ,
              "OP_SEQUENCE" INTEGER NOT NULL ,
              "SQL_OPERATION" VARCHAR(20) NOT NULL ,
              "OBJ_TYPE" VARCHAR(20) NOT NULL ,
              "OBJ_SCHEMA" VARCHAR(129) NOT NULL ,
              "OBJ_NAME" VARCHAR(129) NOT NULL ,
              "OBJ_DEFINER" VARCHAR(129) NOT NULL ,
              "CREATION_TIME" TIMESTAMP ,
              "SQL_STMT" CLOB(2097152) NOT LOGGED NOT COMPACT )  
             IN "USERSPACE1" ;
-- DDL Statements for indexes on Table "SYSTOOLS"."DB2LOOK_INFO"

CREATE INDEX "SYSTOOLS"."DB2LOOK_IND" ON "SYSTOOLS"."DB2LOOK_INFO"
            ("OP_TOKEN" ASC,
             "SQL_OPERATION" ASC,
             "OBJ_TYPE" ASC,
             "OBJ_SCHEMA" ASC,
             "OBJ_NAME" ASC);



------------------------------------------------
-- DDL Statements for table "OSCMEMBERDBSCHEMA"."OSCMEMBERPROFILE"
------------------------------------------------
 
 CREATE TABLE "OSCMEMBERDBSCHEMA"."OSCMEMBERPROFILE"  (
              "ID" INTEGER NOT NULL ,
              "FIRSTNAME" VARCHAR(30) NOT NULL ,
              "MIDNAME" VARCHAR(20) ,
              "LASTNAME" VARCHAR(30) NOT NULL ,
              "TITLERANK" VARCHAR(150) NOT NULL ,
              "CONTACTINFO" VARCHAR(350) NOT NULL ,
              "PHONE" VARCHAR(50) ,
              "EMAIL" VARCHAR(50) NOT NULL ,
              "KEYWORDS" VARCHAR(400) NOT NULL ,
              "DESCRIPTION" LONG VARCHAR NOT NULL ,
              "DPUSERNAME" VARCHAR(50) ,
              "ACTIVENUMBER" SMALLINT NOT NULL WITH DEFAULT 1 ,
              "FAX" VARCHAR(100) NOT NULL ,
              "ADDTIONALTERMS" VARCHAR(350) )  
             IN "USERSPACE1" ;


------------------------------------------------
-- DDL Statements for table "OSCMEMBERDBSCHEMA"."OSCMEMBERKEYWPORD"
------------------------------------------------
 
 CREATE TABLE "OSCMEMBERDBSCHEMA"."OSCMEMBERKEYWPORD"  (
              "ID" INTEGER NOT NULL WITH DEFAULT 1 ,
              "KEYWORDS" VARCHAR(400) ,
              "ADDITIONALKEYWORDS" LONG VARCHAR NOT NULL ,
              "FIRSTNAME" VARCHAR(30) NOT NULL ,
              "MIDNAME" VARCHAR(20) ,
              "LASTNAME" VARCHAR(30) NOT NULL ,
              "TITLE" VARCHAR(150) NOT NULL ,
              "EMAIL" VARCHAR(50) NOT NULL ,
              "PROFILE" VARCHAR(200) )  
             IN "USERSPACE1" ;

-- DDL Statements for primary key on Table "OSCMEMBERDBSCHEMA"."OSCMEMBERKEYWPORD"

ALTER TABLE "OSCMEMBERDBSCHEMA"."OSCMEMBERKEYWPORD"
      ADD PRIMARY KEY
            ("ID");






----------------------------

-- DDL Statements for Views

----------------------------
CREATE VIEW SYSTOOLS.ALTOBJ_INFO_V AS SELECT ALTER_ID, SQL_OPERATION, OBJ_TYPE,
OBJ_SCHEMA, OBJ_NAME, CREATION_TIME, EXEC_MODE, EXEC_SEQ, SQL_STMT, SQL_CODE,
SQL_STATE, SQL_ERRMC FROM SYSTOOLS.ALTOBJ_INFO;
CREATE VIEW SYSTOOLS.DB2LOOK_INFO_V ( op_token, op_sequence, sql_operation,
                       obj_type, obj_schema, obj_name, obj_definer, creation_time,
sql_stmt)               AS SELECT op_token, op_sequence, sql_operation,
obj_type, obj_schema,                         obj_name, obj_definer, creation_time,
sql_stmt               FROM SYSTOOLS.DB2LOOK_INFO;


-------------------------------
-- DDL Statements for Triggers
-------------------------------

CREATE TRIGGER SYSTOOLS.POLICY_DR  NO CASCADE  BEFORE DELETE  ON SYSTOOLS.POLICY
 REFERENCING OLD AS OLD_ROW  FOR EACH ROW  MODE DB2SQL  BEGIN ATOMIC  
 IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY WHERE OLD_ROW.MED = MED AND
OLD_ROW.DECISION = DECISION ) = 1 ) THEN      SIGNAL SQLSTATE '85101' ('At
least one policy is required.');    END IF;  END;

CREATE TRIGGER SYSTOOLS.POLICY_IR  NO CASCADE  BEFORE INSERT  ON SYSTOOLS.POLICY
 REFERENCING NEW AS NEW_ROW  FOR EACH ROW  MODE DB2SQL  BEGIN ATOMIC  
 IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY WHERE NEW_ROW.MED = MED AND
NEW_ROW.DECISION = DECISION ) = 1 ) THEN      SIGNAL SQLSTATE '85101' ('Only
one policy is allowed.');    END IF;  END;

CREATE TRIGGER SYSTOOLS.POLICY_IV  NO CASCADE  BEFORE INSERT  ON SYSTOOLS.POLICY
 REFERENCING NEW AS N  FOR EACH ROW  MODE DB2SQL  BEGIN ATOMIC    VALUES
(SYSPROC.POLICY_VALIDATE(N.MED,N.POLICY));  END;

CREATE TRIGGER SYSTOOLS.POLICY_UV  NO CASCADE  BEFORE UPDATE  ON SYSTOOLS.POLICY
 REFERENCING NEW AS N  FOR EACH ROW  MODE DB2SQL  BEGIN ATOMIC    VALUES
(SYSPROC.POLICY_VALIDATE(N.MED,N.POLICY));  END;



COMMIT WORK;

CONNECT RESET;

TERMINATE;
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17938062
hi

and what is the ouput you get ?
are you sure that oscmdb exists ?
0
 

Author Comment

by:lilyyan
ID: 17938116
may you tell the command for checking a database?
0
 

Author Comment

by:lilyyan
ID: 17938140
the output is:

SQL0104N  An unexpected token "RESET;" was found following "CONNECT".
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17938171
it seems to fail on the last command
can you check if your tables has rows ? were there loaded ?
0
 

Author Comment

by:lilyyan
ID: 17938214
can you check if your tables has rows ?

you mean if the table has some records? one table actually has no records
0
 

Author Comment

by:lilyyan
ID: 17938242
hi,

i just checked, the two tables i created have some records. other table generated by db2, i didn't check
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17938326
sorry
my mistake
we are in the phase of creating the objects
check if all your objects exists - tables, views and trigger
they all should exist since the connect reset is after a commit

so,
does the objects exists ?
0
 

Author Comment

by:lilyyan
ID: 17938356
hi,

how to check objects exists - tables, views and trigger?

i didn't use trigger in my database,
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17938461
what do you mean how to check ?
you know which tables you wish to copy right ?
so check if they exist - use the control center / query systables / query the tables them selfs etc...
0
 

Author Comment

by:lilyyan
ID: 17938516
the tables located in my local pc are for sure exist. but i didn't check if it's also exist in the linux system
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17938604
so check if they exists in the linux system
0
 

Author Comment

by:lilyyan
ID: 17938751
hi,

i checked the objects in linux, and found those objects are exist there. when i click the table folder, i didn't find the tables i created.
0
 

Author Comment

by:lilyyan
ID: 17938765
there are 94 tables by default
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 37

Expert Comment

by:momi_sabag
ID: 17938833

>i checked the objects in linux, and found those objects are exist there. when i click the table folder, i didn't find the >tables i created

so i don't understand, does the objects exist or not ?
0
 

Author Comment

by:lilyyan
ID: 17938852
is there an easy to restore a databse or tables, it's really kind of hurry to me ?
0
 

Author Comment

by:lilyyan
ID: 17938861
there are 94 tables system generated table, but i didn't find the table i created, so they are not there
0
 

Author Comment

by:lilyyan
ID: 17938879
could you please tell the command how to list tables in db2 ?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17938887
if it's a hurry for you
then just open the outputfile
and enter the create table commands by yourself through the command center
can you try that ?
0
 

Author Comment

by:lilyyan
ID: 17938926
so first, i need type db2, then get the db2=> prompt

then type the following commands,  right?

========================
db2=> CONNECT TO OSCMDB;


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


db2=> CREATE TABLE "OSCMEMBERDBSCHEMA"."OSCMEMBERKEYWPORD"  (
              "ID" INTEGER NOT NULL WITH DEFAULT 1 ,
              "KEYWORDS" VARCHAR(400) ,
              "ADDITIONALKEYWORDS" LONG VARCHAR NOT NULL ,
              "FIRSTNAME" VARCHAR(30) NOT NULL ,
              "MIDNAME" VARCHAR(20) ,
              "LASTNAME" VARCHAR(30) NOT NULL ,
              "TITLE" VARCHAR(150) NOT NULL ,
              "EMAIL" VARCHAR(50) NOT NULL ,
              "PROFILE" VARCHAR(200) )  
             IN "USERSPACE1" ;

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

ALTER TABLE "OSCMEMBERDBSCHEMA"."OSCMEMBERKEYWPORD"
      ADD PRIMARY KEY
            ("ID");
0
 

Author Comment

by:lilyyan
ID: 17938946
also i need to craete a schema before create a table, coudld you tell the command for create  schema
0
 

Author Comment

by:lilyyan
ID: 17938995
actully, i still want to try the db2move and db2look,  do you have any suggestion for the error i got ? you moved databse from linux to windows successfully, i think it should also work for me
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17939004
you don't need to create the schema
it will be created by itself

you are correct with the commands
in order to use the commands as you specified , use the command
db2 -t

otherwise, it will think that the new line ends the command. -t tells him that ; ends the command
0
 

Author Comment

by:lilyyan
ID: 17939353
about those objects, would you please have some suggection?
0
 

Author Comment

by:lilyyan
ID: 17939558
when i execute this command:

db2=> CONNECT TO OSCMDB;

the result is: SQL1013N  The database alias name or database name "OSCMDB" could not be
found.  SQLSTATE=42705

then i execute:  db2 create database ocsmdb

the result is:
SQL1005N  The database alias "ocsmdb" already exists in either the local
database directory or system database directory.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17939614
ok

issue the command
db2=> list db directory

if in the list you can see oscmdb, then it exists, and we will think what to do with it

if it does not exists, all you need is to refresh the client cache
issue
db2=> terminate

then try to create it again using the create database command
0
 

Author Comment

by:lilyyan
ID: 17940186
hi,

thanks very much for your rely. i'm trying the commands and will get back after around 1 hour. again, appreciate
0
 

Author Comment

by:lilyyan
ID: 17940460
after i run list db directory, one entry in the result is:
=============================
Database 2 entry:

 Database alias                       = OCSMDB
 Database name                        = OCSMDB
 Local database directory             = /home/db2inst1
 Database release level               = a.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0

=======================
so oscmdb is exist
0
 

Author Comment

by:lilyyan
ID: 17940540
then i run the command: db2 => CONNECT TO OSCMDB

the result is:

SQL1013N  The database alias name or database name "OSCMDB" could not be
found.  SQLSTATE=42705

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17940556
yes
it exists

now i noticed that your scripe says OSCMDB
but the database name is OCSMDB
it's just a misprint
so decide which name does best for you
and either change the script or create a new database

momi
0
 

Author Comment

by:lilyyan
ID: 17941199
you are right, this can be headache! i am so stupid
0
 

Author Comment

by:lilyyan
ID: 17941296
i recreated a database called OSCMDB, so i can run the db2 < outputfile.sql now?
0
 

Author Comment

by:lilyyan
ID: 17941439
why the result is still:

db2 => SQL0104N  An unexpected token "RESET;" was found following "CONNECT".
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601
db2 => DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17942556
yes

so just edit the outputfile and delete that  command
it is optional
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17955924
hi

did you managed to load the data ?
0
 

Author Comment

by:lilyyan
ID: 17960058
hi,

appreciate your reply. i used export and import dada file to load data.

well, i'm still trying the db2look and db2move. i commented out the
CONNECT RESET; in the output file. but no lcuk so far yet,  the error is:

====================================================
DB21034E  The command was processed as an SQL statement because it was no                                              t a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

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

actually, the first line in the outputfile is connect to the database

0
 

Author Comment

by:lilyyan
ID: 17966415
hello momi,

do you still concern this question?
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 500 total points
ID: 17966449
hi
let me make sure i understand
you file begins with a connect command and you get the above output when you run the script ?
can you please try to run it with -v flag so we can see which command fails ?

just run it as
db2 -v -f Outputfile

thanks
0
 

Author Comment

by:lilyyan
ID: 17966651
hi momi, thanks for the prompt reply. i will get back to this question about one hour later
0
 

Author Comment

by:lilyyan
ID: 17967740
hi momi

after run the command db2 -v -f Outputfile.sql

some of the result is:

=============
SQL0104N  An unexpected token "CONSTRAINT" was found following "ADD".
Expected tokens may include:  "CONTACT".  SQLSTATE=42601

("MED",
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

"DECISION",
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

"NAME");
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
0
 

Author Comment

by:lilyyan
ID: 17967751
the Outputfile.sql is:
================================
-- This CLP file was created using DB2LOOK Version 8.2
-- Timestamp: 11/16/2006 3:26:17 PM
-- Database Name: MUSICDB        
-- Database Manager Version: DB2/NT Version 8.2.0          
-- Database Codepage: 1252
-- Database Collating Sequence is: UNIQUE


CONNECT TO MUSICDB;


---------------------------------
-- DDL statements for stored procedures
---------------------------------


CREATE PROCEDURE "MYMUSIC "."SELECTROWS"
            (
            IN      PARM1   VARCHAR(8)
            )
            DYNAMIC RESULT SETS 1
            SPECIFIC SELECTROWSMYMUSIC
            EXTERNAL NAME 'db2pvm!pvm_entry'
            LANGUAGE SQL      
            PARAMETER STYLE GNRLNULL
            NOT DETERMINISTIC
            NOT FENCED NOT THREADSAFE
            MODIFIES SQL DATA
            NO DBINFO;

------------------------------------------------
-- DDL Statements for table "MYMUSIC "."FAVORITES"
------------------------------------------------
 
 CREATE TABLE "MYMUSIC "."FAVORITES"  (
              "ARTIST" VARCHAR(32) NOT NULL ,
              "TITLE" VARCHAR(32) NOT NULL ,
              "CATEGORY" VARCHAR(32) ,
              "ID" INTEGER NOT NULL )  
             IN "USERSPACE1" ;

-- DDL Statements for primary key on Table "MYMUSIC "."FAVORITES"

ALTER TABLE "MYMUSIC "."FAVORITES"
      ADD CONSTRAINT "C4385993" PRIMARY KEY
            ("ID");


------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."HMON_ATM_INFO"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."HMON_ATM_INFO"  (
              "SCHEMA" VARCHAR(128) NOT NULL ,
              "NAME" VARCHAR(128) NOT NULL ,
              "CREATE_TIME" TIMESTAMP NOT NULL ,
              "STATS_FLAG" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "STATS_LOCK" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "STATS_OPTS" INTEGER NOT NULL WITH DEFAULT 0 ,
              "LAST_WAIT" INTEGER NOT NULL WITH DEFAULT 4 ,
              "TO_WAIT" INTEGER NOT NULL WITH DEFAULT 1 ,
              "SIGNAL" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "LEO_TAB" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "EXEC_COUNT" BIGINT NOT NULL WITH DEFAULT 0 ,
              "STATS_STATE" SMALLINT NOT NULL WITH DEFAULT 0 ,
              "STATS_NOTIFY" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "STATS_DETAIL" VARCHAR(200) ,
              "STATS_HISTORY" VARCHAR(200) FOR BIT DATA ,
              "STATS_TIME" TIMESTAMP ,
              "REORG_FLAG" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "REORG_LOCK" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "REORG_STATE" SMALLINT NOT NULL WITH DEFAULT 0 ,
              "REORG_TIME" TIMESTAMP ,
              "REORG_NOTIFY" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "REORG_DETAIL" VARCHAR(200) ,
              "REORG_HISTORY" VARCHAR(200) FOR BIT DATA ,
              "REORG_AVG_RUNTIME" INTEGER )  
             IN "SYSTOOLSPACE" ;


-- DDL Statements for unique constraints on Table "SYSTOOLS"."HMON_ATM_INFO"

ALTER TABLE "SYSTOOLS"."HMON_ATM_INFO"
      ADD CONSTRAINT "ATM_UNIQ" UNIQUE
            ("SCHEMA",
             "NAME");

------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."HMON_COLLECTION"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."HMON_COLLECTION"  (
              "HI_ID" BIGINT NOT NULL ,
              "OBJ_NAME1" VARCHAR(128) NOT NULL ,
              "OBJ_NAME2" VARCHAR(128) NOT NULL WITH DEFAULT '' ,
              "OBJ_NAME3" VARCHAR(128) NOT NULL WITH DEFAULT '' ,
              "OBJ_DETAIL" VARCHAR(512) ,
              "OBJ_STATE" INTEGER NOT NULL ,
              "OBJ_REFRESH_TIME" TIMESTAMP NOT NULL ,
              "NOTIFY" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
              "HISTORY" VARCHAR(200) FOR BIT DATA )  
             IN "SYSTOOLSPACE" ;


-- DDL Statements for unique constraints on Table "SYSTOOLS"."HMON_COLLECTION"

ALTER TABLE "SYSTOOLS"."HMON_COLLECTION"
      ADD CONSTRAINT "HI_OBJ_UNIQ" UNIQUE
            ("HI_ID",
             "OBJ_NAME1",
             "OBJ_NAME2",
             "OBJ_NAME3");

------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."POLICY"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."POLICY"  (
              "MED" VARCHAR(128) NOT NULL ,
              "DECISION" VARCHAR(128) NOT NULL ,
              "NAME" VARCHAR(128) NOT NULL ,
              "UPDATE_TIME" TIMESTAMP NOT NULL ,
              "POLICY" BLOB(2097152) LOGGED NOT COMPACT )  
             IN "SYSTOOLSPACE" ;


-- DDL Statements for unique constraints on Table "SYSTOOLS"."POLICY"

ALTER TABLE "SYSTOOLS"."POLICY"
      ADD CONSTRAINT "POLICY_UNQ" UNIQUE
            ("MED",
             "DECISION",
             "NAME");

------------------------------------------------
-- DDL Statements for table "SYSTOOLS"."STMG_DBSIZE_INFO"
------------------------------------------------
 
 CREATE TABLE "SYSTOOLS"."STMG_DBSIZE_INFO"  (
              "SNAPSHOT_TIMESTAMP" TIMESTAMP NOT NULL ,
              "DB_SIZE" BIGINT NOT NULL ,
              "DB_CAPACITY" BIGINT NOT NULL )  
             IN "SYSTOOLSPACE" ;


-------------------------------
-- DDL Statements for Triggers
-------------------------------

CREATE TRIGGER SYSTOOLS.POLICY_DR  NO CASCADE  BEFORE DELETE  ON SYSTOOLS.POLICY
 REFERENCING OLD AS OLD_ROW  FOR EACH ROW  MODE DB2SQL  BEGIN ATOMIC  
 IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY WHERE OLD_ROW.MED = MED AND
OLD_ROW.DECISION = DECISION ) = 1 ) THEN      SIGNAL SQLSTATE '85101' ('At
least one policy is required.');    END IF;  END;

CREATE TRIGGER SYSTOOLS.POLICY_IR  NO CASCADE  BEFORE INSERT  ON SYSTOOLS.POLICY
 REFERENCING NEW AS NEW_ROW  FOR EACH ROW  MODE DB2SQL  BEGIN ATOMIC  
 IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY WHERE NEW_ROW.MED = MED AND
NEW_ROW.DECISION = DECISION ) = 1 ) THEN      SIGNAL SQLSTATE '85101' ('Only
one policy is allowed.');    END IF;  END;

CREATE TRIGGER SYSTOOLS.POLICY_IV  NO CASCADE  BEFORE INSERT  ON SYSTOOLS.POLICY
 REFERENCING NEW AS N  FOR EACH ROW  MODE DB2SQL  BEGIN ATOMIC    VALUES
(SYSPROC.POLICY_VALIDATE(N.MED,N.POLICY));  END;

CREATE TRIGGER SYSTOOLS.POLICY_UV  NO CASCADE  BEFORE UPDATE  ON SYSTOOLS.POLICY
 REFERENCING NEW AS N  FOR EACH ROW  MODE DB2SQL  BEGIN ATOMIC    VALUES
(SYSPROC.POLICY_VALIDATE(N.MED,N.POLICY));  END;



COMMIT WORK;

--CONNECT RESET;

TERMINATE;
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17968467
hi

if i got it correctly then the failing command is adding a constraint to a table in the systools schema
the command itself seems ok so i don't know what cause this problem, but how about editing the  OutputFile and leaving there only the sql statement that concern your schema ?
you can always tell db2 to create the systools tables so don't worry about that

0
 

Author Comment

by:lilyyan
ID: 17979709
hi momi,

i still don't want to give up this question. is that possible that you have a test to move a simple databass from windows to linux. maybe there is some step i'm missing.


appreciate so much,

lilyyan
0
 

Author Comment

by:lilyyan
ID: 17982717
hi momi,

as you suggested, i commented out the systemtools tables statement in OutputFile.sql. and run the db2 -v -f OutputFile.sql again.

the revised output file is:
--------------------------------------------------------------------------------------------------
CONNECT TO MUSICDB;

CREATE PROCEDURE "MYMUSIC "."SELECTROWS"
            (
            IN      PARM1   VARCHAR(8)
            )
            DYNAMIC RESULT SETS 1
            SPECIFIC SELECTROWSMYMUSIC
            EXTERNAL NAME 'db2pvm!pvm_entry'
            LANGUAGE SQL      
            PARAMETER STYLE GNRLNULL
            NOT DETERMINISTIC
            NOT FENCED NOT THREADSAFE
            MODIFIES SQL DATA
            NO DBINFO;
 
 CREATE TABLE "MYMUSIC "."FAVORITES"  (
              "ARTIST" VARCHAR(32) NOT NULL ,
              "TITLE" VARCHAR(32) NOT NULL ,
              "CATEGORY" VARCHAR(32) ,
              "ID" INTEGER NOT NULL )  
             IN "USERSPACE1" ;


ALTER TABLE "MYMUSIC "."FAVORITES"
      ADD CONSTRAINT "C4385993" PRIMARY KEY
            ("ID");

COMMIT WORK;

TERMINATE;

----------------------------------------------------------------------

the result for executing db2 -v -f OutputFile.sql is in next posting


0
 

Author Comment

by:lilyyan
ID: 17982728
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

CONNECT TO MUSICDB;
SQL1001N  "MUSICDB;" is not a valid database name.  SQLSTATE=2E000

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003


CREATE PROCEDURE "MYMUSIC "."SELECTROWS"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
(
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

IN      PARM1   VARCHAR(8)
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

)
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

DYNAMIC RESULT SETS 1
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

SPECIFIC SELECTROWSMYMUSIC
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

EXTERNAL NAME 'db2pvm!pvm_entry'
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

LANGUAGE SQL
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

PARAMETER STYLE GNRLNULL
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

NOT DETERMINISTIC
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

NOT FENCED NOT THREADSAFE
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

MODIFIES SQL DATA
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

NO DBINFO;
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003



CREATE TABLE "MYMUSIC "."FAVORITES"  (
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

"ARTIST" VARCHAR(32) NOT NULL ,
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

"TITLE" VARCHAR(32) NOT NULL ,
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

"CATEGORY" VARCHAR(32) ,
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

"ID" INTEGER NOT NULL )
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

IN "USERSPACE1" ;
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003


DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003


DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003


DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

ALTER TABLE "MYMUSIC "."FAVORITES"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

ADD CONSTRAINT "C4385993" PRIMARY KEY
SQL0104N  An unexpected token "CONSTRAINT" was found following "ADD".
Expected tokens may include:  "CONTACT".  SQLSTATE=42601

("ID");
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17985312
hi

i think i know what is the problem
try run the script with the -t flag
that is

db2 -t -v -f Outpufile


i have successfully trasfered a db between linux and windows this way

momi
0
 

Author Comment

by:lilyyan
ID: 17986950
hi momi:

this time it's amazing. the -t flag works.  

when i run this command: db2 -t -v -f Outpufile.sql

the result is: DB21007E  End of file reached while reading the command.


but when i run:  db2 musicdb import

the result is:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003



0
 

Author Comment

by:lilyyan
ID: 17986999
then i re-run the import command as following:

db2 connect to musicdb

the result is:

Database Connection Information

 Database server        = DB2/LINUX 8.1.6
 SQL authorization ID   = DB2INST1
 Local database alias   = MUSICDB

db2 MUSICDB import

the result is:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "MUSICDB
import".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17987123
hi
i'm glad it's work
it does not work because that is not the way to import
if you'll look in the top of this question, you'll see that in order to import you need to use the db2move command
go to the windows command line and enter
db2move musicdb import

momi
0
 

Author Comment

by:lilyyan
ID: 17987172
hi momi,

i just found this questions also

>go to the windows command line and enter
db2move musicdb import

well, i should run the command in linux, as i'm transferring data from windwos to linux
0
 

Author Comment

by:lilyyan
ID: 17987200
hi momi,

i just run the above command, the result is:

db2move MUSICDB import

*****  DB2MOVE  *****

Action:      IMPORT

Start time:  Tue Nov 21 09:12:38 2006


Connecting to database MUSICDB ... successful!  Server: DB2 Common Server V8.1.6

Binding package automatically ...
Bind file: /home/db2inst1/sqllib/bnd/db2move.bnd

Bind was successful!


***  Table "MYMUSIC "."FAVORITES":  ERROR 3107.  Check message file tab1.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  There is at least one warning message in the message file.


***  Table "SYSTOOLS"."HMON_ATM_INFO":  ERROR 3107.  Check message file tab2.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  There is at least one warning message in the message file.


***  Table "SYSTOOLS"."HMON_COLLECTION":  ERROR 3107.  Check message file tab3.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  There is at least one warning message in the message file.


***  Table "SYSTOOLS"."POLICY":  ERROR 3107.  Check message file tab4.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  There is at least one warning message in the message file.


***  Table "SYSTOOLS"."STMG_DBSIZE_INFO":  ERROR 3107.  Check message file tab5.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  There is at least one warning message in the message file.


Disconnecting from database ... successful!

End time:  Tue Nov 21 09:12:38 2006


===========================
it seems there are two errors, but the command is working. this is really great, appreciate so much !
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17987257
what does tab1.msg file says ?
0
 

Author Comment

by:lilyyan
ID: 17987515
sorry for a late bit response. i've not use vi edit for a long time

the the tab1.msg is:

SQL3104N  The Export utility is beginning to export data to file "tab1.ixf".^M
^M
SQL3105N  The Export utility has finished exporting "3" rows.^M
^M
SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date
"20061116", and time "152718".

SQL3050W  Conversions on the data will be made between the IXF file code page
"1252" and the application code page "819".

SQL3153N  The T record in the PC/IXF file has name "tab1.ixf", qualifier "",
and source "            ".

SQL3109N  The utility is beginning to load data from file "tab1.ixf".

SQL3110N  The utility has completed processing.  "3" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "3".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "3" rows were processed from the input file.  "3" rows were
successfully inserted into the table.  "0" rows were rejected.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17987656
ok
so it seems it loaded all your rows

so all the data was transfered ok ?
did we taimed the beast ?
0
 

Author Comment

by:lilyyan
ID: 17987657
there isn't an error in the tab1.msg file though
0
 

Author Comment

by:lilyyan
ID: 17987670
but when the db2move musicdb import was execute, there is a warning: SQL Warning!  SQLCODE is  3107
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17987714
yep
that warning messages says that there is a warning in one of the files
the warning in the message file is about the data being converted between code pages
you can just select it from the tables to see if the data was loaded ok
if you use only english characters, you should encounter no error
and everything should be ok
0
 

Author Comment

by:lilyyan
ID: 17987743
hi momi,

this is so great that the data transfer is perfectly ok now.

before this question is close, i still have a few questions to ask for ( the point should be 800 : )


q1. essesnlty, the db2move and db2look tool can be used for transfer data between diferrently operating system ?

from this link: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002079.htm

it says : This tool facilitates the movement of large numbers of tables between DB2 databases located on workstations. The tool queries the system catalog tables for a particular database and compiles a list of all user tables. It then exports these tables in PC/IXF format. The PC/IXF files can be imported or loaded to another local DB2 database on the same system, or can be transferred to another workstation platform and imported or loaded to a DB2 database on that platform.


what dose "another workstation platform" mean?


---------------------------------------------------------------
q2. what does -t -v and  -f  mean in db2 -t -v -f OutputFile.sql ?


0
 

Author Comment

by:lilyyan
ID: 17987789
sorry for the typo, the question one is:

q1. essentially, do you think the db2move and db2look tool can be used for transfer data between differently operating system ?
0
 

Author Comment

by:lilyyan
ID: 17987793
yeah, i'm only using english characters
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 500 total points
ID: 17987811
another workstation platform mean exactly what you asked - another operating system

the -t say that the sql terminator (that is the character that tells db2 that the sql statement ends) should be ; instead of <new-line> which is the default (that's why when you run without -t you get an error message for each line that says that this is an unknown command)
-v says - print the commands that you perform, and for each command, the output
-f means use input file, in our case OutputFile.sql
0
 

Author Comment

by:lilyyan
ID: 17987912
hi momi,

so i guess db2move and db2look can be safely used to transfer data between different operating systems.  again appreciate
so much for your great help !

i might ask another question about making csv file in db2, would you please answer if possible
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17987991
yep
you can use db2move and db2look to transfer between different operating systems
is order to get data in csv format you can use db2 export command
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/core/r0008303.htm
you have in the and an example that modify the delimited output paramters
take a look at that
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now