Topic
5 replies Latest Post - ‏2012-03-20T21:40:27Z by nivanov1
SystemAdmin
SystemAdmin
708 Posts
ACCEPTED ANSWER

Pinned topic Neither create table nor tablespace: is it a setup problem ?

‏2012-03-14T15:06:31Z |
Dear Colleagues,
this is the first time I'm using the enablement for an Oracle based job to be moved into Linux - DB2 platform.
I got errors here reported at point 5, and another at point 6; in points 1-to-4 I describe precisely the performed steps to set up my environment.
I appreciate any comment and support to solve the issues.

1) initial condition "just coming from DB2 trial installation - one instance - no defined database"

$ db2set -all
[i] DB2COMM=tcpip
[i] DB2AUTOSTART=YES
[g] DB2SYSTEM=DB2-Ubu64
[g] DB2INSTDEF=db2inst1
[g] DB2ADMINSERVER=dasusr1

$ db2ilist
db2inst1

$ db2 get instance
The current database manager instance is: db2inst1

$ db2start
SQL1026N The database manager is already active.

$ db2 list db directory
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031

2) set up of Oracle compatibility

$ db2set DB2_COMPATIBILITY_VECTOR=ORA
$ db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES

$ db2set -all
[i] DB2_DEFERRED_PREPARE_SEMANTICS=YES
[i] DB2_COMPATIBILITY_VECTOR=ORA
[i] DB2COMM=tcpip
[i] DB2AUTOSTART=YES
[g] DB2SYSTEM=DB2-Ubu64
[g] DB2INSTDEF=db2inst1
[g] DB2ADMINSERVER=dasusr1
$

$ db2stop
SQL1064N DB2STOP processing was successful.
$ db2start
SQL8007W There are "87" day(s) left in the evaluation period for the product "DB2 Connect Server". For evaluation license terms and conditions, refer to the License Agreement document located in the license directory in the
installation path of this product. If you have licensed this product, ensure the license key is properly registered. You can register the license via the License Center or db2licm command line utility. The license key can be
obtained from your licensed product CD.
SQL1063N DB2START processing was successful.

$ db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09075"
with level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23292", and Fix Pack
"5".
Product is installed at "/opt/ibm/db2/V9.7".

3) Create an Oracle compatible database

$ db2 create database pcspv pagesize 32 k
DB20000I The CREATE DATABASE command completed successfully.

db2 => list db directory

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias = PCSPV
Database name = PCSPV
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

4) working on the Oracle-compatible database: run under clpplus script programs using production statements

$ clpplus
CLPPlus: Versione 1.5
Copyright (c) 2009, 2011, IBM CORPORATION. Tutti i diritti riservati.

and run the script01: @/home/db2inst1/Documenti/script01.sql

the script is:

connect db2inst1@localhost:50000/pcspv

create table employee (EMPNO NUMBER(5),ENAME VARCHAR2(50),DEPTNO NUMBER(5));

CREATE TABLE PCSCLASSBOM
(
CLASSBOMID NUMBER(20) NOT NULL,
CLASSID NUMBER(20) NOT NULL,
CHILDID NUMBER(20) NOT NULL,
CREATEDATA DATE,
LASTUPDATEDATA DATE
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

and then run the script02.sql that is:

connect db2inst1@localhost:50000/pcspv

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/home/db2inst1/db-data.DBF' SIZE 374M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE USERS DATAFILE
'/home/db2inst1/db-data1.DBF' SIZE 2780M AUTOEXTEND ON NEXT 10M MAXSIZE 5120M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
CREATE PROFILE DEFAULT LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED

5) the clpplus messages when running the scrip01 are here reported:

DB250000I: Il comando è stato completato correttamente. *****the first verb create table has been successfully executed *****

Errore accanto alla riga 4: *****the second verb create table failed ***** due to:

SQL0104N An unexpected token "LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MON" was found following "TUPDATEDATA DATE
)
". Expected tokens may include: "<space>".
6) the clpplus messages when running the scrip02 are here reported:

Errore accanto alla riga 4:
SQL0104N An unexpected token "TEMPFILE
'/home/db2inst1/db-data.DBF' SIZ" was found following "RARY TABLESPACE TEMP". Expected tokens may include: "<space>".
Errore accanto alla riga 10:
SQL0104N An unexpected token "DATAFILE
'/home/db2inst1/db-data1.DBF' SI" was found following "ATE TABLESPACE USERS". Expected tokens may include: "<space>".
Errore accanto alla riga 21:
SQL0104N An unexpected token "CREATE PROFILE" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".

therefore no tablespace has been generated as well as the profile.

Does it depend on the environment setup ?
Updated on 2012-03-20T21:40:27Z at 2012-03-20T21:40:27Z by nivanov1
  • nivanov1
    nivanov1
    22 Posts
    ACCEPTED ANSWER

    Re: Neither create table nor tablespace: is it a setup problem ?

    ‏2012-03-15T21:25:15Z  in response to SystemAdmin
    CREATE TABLESPACE syntax is quite different in DB2 from what you might expect in Oracle, due to the differences in physical layout of data. To simplify things you can omit all those physical parameters because automatic storage management is enabled by default in DB2. Note that there are two types of temporary tablespaces in DB2: "user" - for temporary tables you create, and "system" - for DB2 internal use. One system temporary tablespace is created by default.

    So, your statements would look like:

    CREATE USER TEMPORARY TABLESPACE TEMP;
    CREATE TABLESPACE USERS;
    • SystemAdmin
      SystemAdmin
      708 Posts
      ACCEPTED ANSWER

      Re: Neither create table nor tablespace: is it a setup problem ?

      ‏2012-03-19T09:46:12Z  in response to nivanov1
      Thanks Ivanov1, I get the point and now I'll try to map the options inside the create tablespace statement of the customer into the implicit or explicit DB2
      corresponding features.
      This applies to
      LOGGING, DATAFILE, SIZE, REUSE, AUTOEXTEND ON, NEXT
      and MAXSIZE UNLIMITED, EXTENT MANAGEMENT LOCAL, SEGMENT SPACE MANAGEMENT AUTO.
      Anyway, by means of your suggestion the step of create tablespace is done. Inside operations management I'll complete the migration of the statement.

      And now, have you any comment about the create table issue ? I have a lot (more tens) statements very similar to the following ones:
      CREATE TABLE PCSCLASSBOM
      (
      CLASSBOMID NUMBER(20) NOT NULL,
      CLASSID NUMBER(20) NOT NULL,
      CHILDID NUMBER(20) NOT NULL,
      CREATEDATA DATE,
      LASTUPDATEDATA DATE
      )
      LOGGING
      NOCOMPRESS
      NOCACHE
      NOPARALLEL
      MONITORING;

      It appears that LOGGING is a not accepted / recognized clause.
      Thanks, c.l.
      • nivanov1
        nivanov1
        22 Posts
        ACCEPTED ANSWER

        Re: Neither create table nor tablespace: is it a setup problem ?

        ‏2012-03-19T15:23:18Z  in response to SystemAdmin
        It's the same story again - Oracle-specific clauses to the table DDL statements are not supported because they don't make sense in DB2, due to the physical differences. If you use IDMT for migration, it will automatically strip the unnecessary clauses, otherwise you'll need to edit (remove) them manually.

        It might also be a good idea to check the DB2 manuals for the correct statement syntax: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
        • SystemAdmin
          SystemAdmin
          708 Posts
          ACCEPTED ANSWER

          Re: Neither create table nor tablespace: is it a setup problem ?

          ‏2012-03-19T21:29:18Z  in response to nivanov1
          Thanks again nivanov1 for your answer.
          My assumption was that we can execute every Oracle plsql statement inside clpplus, therefore also those that generate the schema of my customer. Now I know that it is not true, some statements totally lawful in Oracle, are not executed as is in DB2 and discarded by clpplus. Really I expected something like that, expecially and only for very complex statements.
          My initial approach after having suspended the migration and being started the application enablement was based on executing the scripts that generate the production schema in Oracle by means of the clpplus feature to execute plsql scripts. Probably my expectation was to obtain a coverage degree or a similar indicator for each plsql statement.
          But now I have to change the approach. If I correctly understand, I need to connect immediately to the Oracle production environment with the IDMT in order to generate the equivalent set of scripts that allows to build the first DB2 migrated server. In such a way, how can we identify the Oracle statements partially covered by the DB2 ?
          Anyway, let's go to apply IDMT.
          c.l.
          • nivanov1
            nivanov1
            22 Posts
            ACCEPTED ANSWER

            Re: Neither create table nor tablespace: is it a setup problem ?

            ‏2012-03-20T21:40:27Z  in response to SystemAdmin
            There is a tool that helps you analyze the compatibility level of your Oracle code: http://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=swg-meetdb2

            You can extract your Oracle schema and PL/SQL by any method that you have available, not necessarily IDMT.

            You may also want to read materials at http://www.ibm.com/developerworks/training/kp/dm-kp-oracletodb2migration/