Results 1 to 4 of 4

Thread: SQL help

  1. #1
    Join Date
    Oct 2004

    Unanswered: SQL help

    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.


  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    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

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 5
    You can try:


    at the start of your program.



  4. #4
    Join Date
    Oct 2004
    Thank you guys.
    Its working well.i am connecting to database using db2inst1 user and its working well.

    have a gr8 weekend here.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts