Re: Question for DBA who do both Oracle and DB2

From: James A. Williams <willjamu_at_mindspring.com>
Date: Sat, 05 Oct 2002 02:25:10 GMT
Message-ID: <3d9e4d18.2467027@news.mindspring.com>

On 4 Oct 2002 07:25:51 -0700, sheemat_at_yahoo.com (Mathew) wrote:

Control Center is pretty cool and much Better than OEM IMO. I do most of my Oracle work using command line. Its a matter of learning the commands.

Below is from an Article.

>IRAMAKRISHNA KOLLURU

With these reference tables, you can translate your Oracle skills into a DB2 future.
Learning the Lingo
You’ve honed and polished your Oracle DBA skills over the years. Then one day, something happens.
Your company decides to migrate to a new database. Or, you decide to migrate from your
company and want to expand your skills. Do you have to start over from the beginning?
Not if you’re migrating to DB2 Universal Database (DB2 UDB). IBM provides a number of resources
to help DBAs transfer their skills to the DB2 environment, including a free, self-study course for relational
DBAs that you can download from the Web. (For a complete list of IBM migration resources, go to ibm.
com/software/data/db2/migration.)
I’ve put together several reference tables that you can keep by your side as you start to explore DB2:
Table 1 (page 50) compares the different approaches to databases and instances. Table 2 (page 52)
shows the DB2 equivalents for common Oracle commands. I’ve also written sample code for moving
Oracle data to DB2, which you can read in the online version of this article (www.db2mag.com/db_area/
archives/2002/q1/kolluru.shtml). Please note: These tables cover DB2 UDB v.7.1 for Windows NT and
Oracle 8.0 and higher.You can find more detailed information in the DB2 reference manuals available
online.
So dust off your skill set and get ready to update. www.db2mag.com DB2 magazine 49
Users Users are created by the database. Users are created at the operating system level.
Operating system users are possible.
Instances You can have one database per instance. One instance can have multiple databases.
(However, in Oracle Parallel Server, you Use the following to create them:
can have multiple instances for one
database.) Create set db2instance=inst1
db2icrt inst1
Delete db2idrop inst1
List db2ilist
Databases Create database doesn’t create any Create database creates three default
default tablespaces. tablespaces: SYSCATSPACE,TEMPSPACE1, and USERSPACE1.
Create set db2instance=inst1
db2start
db2cmd /c /w /i db2 create database db1
Drop db2cmd /c /w /i db2 drop db db1
List db2cmd /c /w /i db2 list db directory Sample Oracle provides Scott, a sample schema DB2 provides the SAMPLE database
where new users can learn SQL. for learning and testing. Here’s how to create it:
set db2instance=inst1
db2sampl
Tablespaces Data files are managed by the database. DB2 uses two kinds of tablespaces:
Oracle tablespaces are similar to DB2 System Managed Storage (SMS) and DMS.
Database Managed Storage (DMS). Objects (for example, tables and indexes) placed on
SMS tables are individual files.Containers in DB2 are synonymous with data files in Oracle. Examples: Create
db2cmd /c /w /i db2
connect to db1
SMS (where d:\inst1\systab is the directory location): create regular tablespace \
db1tab managed by system using \
(‘d:\inst1\systab’)
DMS (where systab.dbf is the container of size 5120 and 4k page size is the default.):
create regular tablespace db1tab \
managed by database using \
( file ‘d:\inst1\systab.dbf’ 5120)
number(precision,scale) numeric(precision,scale)or decimal(p,s) varchar2(max 4000) varchar(max 32672) – page size 32k

blob blob(max size) not logged compact*
clob clob(max size) not logged compact
long clob(max size) not logged compact

long raw blob(max size) not logged compact date timestamp
*Not logged compact saves space and
transactions aren’t logged in the redo logs. Database
Oracle DB2
DATABASES AND INSTANCES
50 DB2 magazine QUARTER 1, 2002
select sysdate from dual; select current timestamp from sysibm.sydummy1
select current date from sysibm.sysdummy1 select user from dual; select user from sysibm.sysdummy1 sqlplus scott/tiger @test.sql db2 -f test.sql +v -l test.log –t (Put connect db statement in test.sql; test.log is the log file.) truncate table emp; Method 1
a. create table emp(a numeric) not logged initially; b. alter table emp activate not logged initially \ Method 2
a. Create an empty file, d:\temp\test.del b. import from d:\temp\test.del of del modified \ by chardel”” and replace into emp
select ename from emp where rownum <10; select ename from (select ename,row_number() \ over() as rownum
from emp) as temp where \ rownum <10
select table_name from user_tables; select name from sysibm.systables where \ type=’T’ and
creator=user
select view_name from user_views; select name from sysibm.systables where \
type=’V’ and creator=user
insert into emp(empno) values(10); insert into emp(empno) values (10),(11)
insert into emp(empno) values(11);
set ORACLE_SID =inst1 set db2instance=inst1 svrmgrl > connect internal db2start
svrmgrl>startup
set ORACLE_SID =inst1 set db2instance=inst1 svrmgrl>connect internal db2stop
svrmgrl>shutdown
set ORACLE_SID =inst1 set db2instance=inst1 svrmgrl>connect internal db2stop force
svrmgrl>shutdown immediate
select select case deptno \
decode(deptno,10,’Mkt’,20,’Engg’, when 10 then ‘Mkt’ \ ‘Fin’) from emp; when 20 then ‘Engg’ \
else ‘Fin’ \
end as Dept \
from emp
select ename, loc select ename,loc \
from dept, emp from \
where dept.deptno = dept left outer join emp on \ emp.deptno(+) dept.deptno = emp.deptno \ and emp.deptno is null; where \
emp.deptno is null
select nvl(comm,0) commision select coalesce(comm,0) as commission from emp
from emp;
alter table emp disable constraint set integrity for emp foreign key immediate \ unchecked
fk_dept;
Oracle DB2
RESOURCES:
DB2 DBA Self-Study Course
ibm.com/software/data/db2/selfstudy/
index.html
DB2 Reference Manuals
ibm.com/cgi-bin/db2www/data/db2/
udb/winos2unix/support/v7pubs.d2w/
en_main
Ramakrishna Kolluru is a
senior database engineer with
SeeCommerce.com in Palo Alto,
Calif. You can reach him at
rkolluru_at_seecommerce.com.
CONVERTING ORACLE COMMANDSTO DB2
52 DB2 magazine QUARTER 1, 2002 Received on Fri Oct 04 2002 - 21:25:10 CDT