Db2

 View Only
  • 1.  [HELP] Problem from DB2 V11.5 Server Edition on Red Hat Linux 7.7

    Posted Mon November 11, 2019 09:57 AM
    Hi, I'm new to DB2 and have less knowledge about database and Linux. And my English is very poor. This is the first time of my whole life to ask for some help about tech problem in English. So if there are any syntax error and/or something weird, please forgive me. And I really need your help. I've fought for this for 3 weeks and nobody can help me, so I come to here.

    BASIC INFORMATION:

    OS: Red Hat Linux 7.7
    DATABASE: IBM DB2 V11.5 Server Edition

    BACKGROUND:

    My target is  DB2 database installation on Linux Server and data transfer.

    I've installed a database by using db2setup, which has a GUI. It was ROOT INSTALLATION(I guess) and created two users, db2inst1 and db2fenc1. And actually it installed the database on user db2inst1, so I can use the command su - db2inst1 and db2start to start the database service. Then I open the port 50000/tcp and it works well, I can connect to the database Sample(which is created by db2 command db2sampl) remotely from my client PC.

    Then, I have to run some Shell scripts to finish the whole work. It is about the data transfer. I have no idea about how it works and what it exactly does. The Shell scripts are written by someone else and  prepared for DB2 V11.1. My colleague told me just run it and it would work automatically. But I found I met the dilemma.

    PROBLEM:

    There are codes in Shell script file:
    ...

    db2 CONNECT TO ${DBNAME} USER ${USERID} USING ${PASSWORD}

    ...

    db2 "CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE_16K PAGESIZE 16384 MANAGED BY SYSTEM USING ('${TSPATH}/TEMPSPACE_16K') BUFFERPOOL BUF_16K EXTENTSIZE 16"

    ...

    db2 "CREATE REGULAR TABLESPACE XXX PAGESIZE 8192 MANAGED BY DATABASE USING (FILE '${TSPATH}/XXX' 100000) BUFFERPOOL BUF_8K EXTENTSIZE 32"

    ...


    Here is the error imformation from Linux command line:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0109N The statement or command was not processed because the following
    clause is not supported in the context where it is used: "MANAGED BY
    DATABASE". SQLSTATE=42601

    I've googled key words "SQL0109N", "SQLSTATE=42601", "CREATE TABLESPACE MANAGED BY DATABASE" and so on, and looked through the official document and support. But there is NO help for me. Some told me that I should remove the clause "MANAGED BY DATABASE", but after that a new problem comes to me:

    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 "USING (FILE '/database/DCISPL16/TS_EMR_1'" was
    found following "_EMR_1 PAGESIZE 8192". Expected tokens may include:
    "<space>". SQLSTATE=42601

    So I guess it's not the problem about the clause "MANAGED BY DATABASE". After all, the SQL statement above has a "MANAGED BY SYSTEM" and it worked and no errors! Maybe this is about DB2 version. I'm not sure. Or the original Shell scripts are wrong and I have no ability to recognize that.

    Now, I don't know what to do. Anyone can help me? If it is possible, please tell me both the way to solve this problem and the reason why this happened.

    Thanks a lot!

    ------------------------------
    Da Wei Liu
    ------------------------------

    #Db2


  • 2.  RE: [HELP] Problem from DB2 V11.5 Server Edition on Red Hat Linux 7.7

    Posted Tue November 12, 2019 02:26 AM
    it would be nice to have some knowledge about shell scripting and db2.  first of all , the variable in the connect statement are probably supplied when invoking the script.  another remark would be : never use regular tablespaces anymore, they are depricated since long time.
    always use automatic storage - have a look at knowledge center of db2 ...
    it seems very strange that you as novice in db2/shell has to maintain a db2 implementation/outsourced..
    but anyhow : reading - reading - reading is the recommendation...

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------



  • 3.  RE: [HELP] Problem from DB2 V11.5 Server Edition on Red Hat Linux 7.7

    Posted Tue November 12, 2019 02:29 AM
    this is the link for knowledge center :
    https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.kc.doc/welcome.html

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------



  • 4.  RE: [HELP] Problem from DB2 V11.5 Server Edition on Red Hat Linux 7.7

    Posted Sun November 17, 2019 09:45 PM
    Thank you for your help! At the very beginning, nobody noticed that DB2's update would impact those scripts. But it does. And we can't find the original author, the best candidate to handle this task. Now I'm trying to learn DB2 knowledge. As a beginner, there are many challenges to face.

    ------------------------------
    Da Wei Liu
    ------------------------------



  • 5.  RE: [HELP] Problem from DB2 V11.5 Server Edition on Red Hat Linux 7.7

    Posted Tue November 12, 2019 04:37 AM
    Hi ,
    your script is no longer valid for the userspace since Db2 V11. no longer supports Tablespaces managed by database.
    The commands you are showing are also not consistent.
    1. I miss the bufferpool information.
    2. You tempspace is 16k pagesize and the userspace is 8k

    You always need a bufferpool and  a userspace of the same pagesize . And normally you also define the tempspace with the same pagesize.

    What do you want to do?
    Your database is there.

    yours
    Joachim

    ------------------------------
    Joachim Gerolf Stumpf
    ------------------------------



  • 6.  RE: [HELP] Problem from DB2 V11.5 Server Edition on Red Hat Linux 7.7

    Posted Tue November 12, 2019 07:12 AM
    Hi,
    for more documentation you can use the Db2 knowledge center
    https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.welcome.doc/doc/welcome.html

    There you can search for each command and get samples.

    You can check the create database command of the database using
    db2look -d sample -createdb -o createdatabase-statement.ddl
    and additional tablespaces
    db2look -d sample -l -o tablespaces.ddl

    You can check for additional tablespaces and bufferpools also with
    db2pd -d sample -tablespaces
    db2pd -d sample -bufferpools (here are also internal bufferpools displayed. Use only where Automatic is true)

    sample minimum tablespace statement
    db2 connect to sample
    db2 'create regular tablespace userspace2 managed by automatic storage' 



    ------------------------------
    Joachim Gerolf Stumpf
    ------------------------------



  • 7.  RE: [HELP] Problem from DB2 V11.5 Server Edition on Red Hat Linux 7.7

    Posted Tue November 12, 2019 08:03 AM
    I would not recommend to use regular tablespaces anymore.
    there is a limitation of 256 rows/page.
    LARGE is the default

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------



  • 8.  RE: [HELP] Problem from DB2 V11.5 Server Edition on Red Hat Linux 7.7

    Posted Sun November 17, 2019 09:59 PM
    Thank you for your help! I truly have the matched definitions and I just forgot to paste them completely. And your examples helped a lot, thanks again.

    ------------------------------
    Da Wei Liu
    ------------------------------