Hi I have db2 v7.1 installed on redhat linux and application written in java. Its working perfectly well without any problems.
I am making duplicate copy of system on windows 2000 server. I loaded db2 v7.1 on windows 2000 server and imported database from linux to windows using db2move import export commands. Database is successfully imported with all records without any errors
Now when i am trying to run some sqls on windows 2000 server using java i have to change the sql little bit to work
I have to add instancename in front of each table name then it works.
e.g. on linux system "Select * From employee" works and it doesnt work on windows 2000 unless I modify sql statement to "Select * From DB2INST1.employee"
What is the way to get read of that SQL change. I have hundres of servlets. I dont want to modify each every SQL
Please show me some way to do it.
That thing "in front of each table name" is called a schema. When you create a table without specifying a schema the schema name will be set to the current user name.
For example, if you're connected as user DB2INST1 and issue "CREATE TABLE TEST..." in fact DB2 will create a table named "DB2INST1.TEST". If you then connect as user ANOTHERUSER and issue "DESCRIBE TABLE TEST" you'll get an error because DB2 will in fact be looking for a table named "ANOTHERUSER.TEST". To make sure you're looking for the right table you need to specify schema name: "DESCRIBE TABLE DB2INST1.TEST".
To solve your problem you'll need to do one of the following:
- have your application connect to the database as DB2INST1;
- when you create the tables make sure they are created in a schema matching the login id that the application used to connect to the database;
- issue "SET CURRENT SCHEMA DB2INST1" in the application before attempting any other statements;
- create aliases for all DB2INST1 tables in the schema of the application user: CREATE ALIAS ANOTHERUSER.TABLE1 FOR DB2INST1.TABLE1