Db2 UDB and Raw Devices!!!
A Log
particular topic??.
TIA,
A Log.
Ronald Cole
> Hi Folks, Are there any interesting articles or information on this
> particular topic??.
Yes, the instructions given for Linux in one of the manuals open your
database up to being clobbered by any user on the system! I posted
the URL to IBM's online documentation and asked about that here in
this newsgroup several weeks ago and was most thoroughly ignored.
IMO, raw device support in DB2 isn't ready for prime-time. Informix,
on the other hand works wonderfully on raw devices.
--
Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412
Ronald Cole <ron...@forte-intl.com> Phone: (760) 499-9142
President, CEO Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B
Bob Harbus
using raw devices on many platforms with no issues ... so I am curious
about your blanket statement and what issues you feel exist either in the
documentation or your experiences?
Thanks for your time ... Bob
Knut Stolze
> Ronald - can you elaborate on your statements? We have many many customers
> using raw devices on many platforms with no issues ... so I am curious
> about your blanket statement and what issues you feel exist either in the
> documentation or your experiences?
Ronald is referring to some documentation which supposedly stated to set the
file access flags for the raw device to rw-rw-rw-. (I say "supposedly"
because I haven't read the doc myself). This is indeed not the most secure
configuration.
I don't understand the "not ready for prime-time" argument either.
>> Yes, the instructions given for Linux in one of the manuals open your
>> database up to being clobbered by any user on the system! I posted
>> the URL to IBM's online documentation and asked about that here in
>> this newsgroup several weeks ago and was most thoroughly ignored.
Well, everyone posting here does this on his own time and will. So please
see it under that light. Here you will get everything only on a best-effort
and who-thinks-to-know-it basis. If you want to have ensured and dedicated
help, call IBM support.
--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab
Ronald Cole
> On Fri, 05 Apr 2002 10:04:22 -0500, Bob Harbus wrote:
> > Ronald - can you elaborate on your statements? We have many many customers
> > using raw devices on many platforms with no issues ... so I am curious
> > about your blanket statement and what issues you feel exist either in the
> > documentation or your experiences?
>
> Ronald is referring to some documentation which supposedly stated to set the
> file access flags for the raw device to rw-rw-rw-. (I say "supposedly"
> because I haven't read the doc myself). This is indeed not the most secure
> configuration.
Directions are in "Administration Guide V7.2, Volume 2:
Implementation" (aka db2d2e71.pdf) starting on page 115.
Not only are the permissions of the raw device suspect, but the answer
given for the calculation of num_pages is wrong (it's one billion too
many).
> I don't understand the "not ready for prime-time" argument either.
Easy. Even with the obviously poor choice of permissions in the
documentation, the command did not even work for me, a relative DB2
newbie. For Informix IDS, you have to tell the engine the path of the
database and initialize it before you can create schemas (in Informix
parlance, a database is a collection of "chunks" and a schema is a
"database").
After installing Informix IDS, I just had to edit
$INFORMIXDIR/etc/$ONCONFIG and change the following value from it's
default to:
ROOTSIZE 1052226 # Size of root dbspace (Kbytes)
and then I had to:
# chown informix.informix /dev/raw/raw1
# chmod 660 /dev/raw/raw1
# ln -s /dev/raw/raw1 /dev/online_root
# $INFORMIXDIR/bin/oninit -ivy
...
$ $INFORMIXDIR/bin/dbaccess - - <<-EOF
create database fubar;
EOF
and, voila, I had a database named "fubar" on a raw partition. I
could even have easily made the engine run on a loopback device just
by setting permissions on a file, running "losetup", and changing the
symbolic link and ROOTSIZE. It's all pretty easy *and* documented.
For my foray into DB2, the book "The Complete Reference: DB2" by
Melnyk likewise suggests that you need to create a "tablespace" before
you can create a "database" (page 159). I even believe I understood
the difference between a System-Managed Space Table Space and a
Database-Managed Space Table Space. So, I installed DB2-7.1 PDE and
fixpaks 3 and 6, carefully followed the "DB2 for Linux HOWTO" and then
did:
===============================================================================
[ronald@yakisoba ronald]$ su - db2inst1
Password:
[db2inst1@yakisoba db2inst1]$ . $HOME/sqllib/db2profile
[db2inst1@yakisoba db2inst1]$ db2
(c) Copyright IBM Corporation 1993,2001
Command Line Processor for DB2 SDK 7.2.4
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => create tablespace dms1 managed by database using (device '/dev/raw/raw2' 263056)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1024N A database connection does not exist. SQLSTATE=08003
db2 =>
===============================================================================
Posting an inquiry here garnered no responses whatsoever... even one
that says "you're wrong". So, I surmised that raw devices on DB2 are
not a popular option (just like Oracle downplays them) and that is why
the errors in the documentation weren't caught and why I got no
response to my post.
> Well, everyone posting here does this on his own time and will. So please
> see it under that light. Here you will get everything only on a best-effort
> and who-thinks-to-know-it basis. If you want to have ensured and dedicated
> help, call IBM support.
I have no doubt that if I were not evaluating db2 via the personal
development edition and had paid for support, then IBM would provide
me with the esoteric instructions to get it running and in a secure
fashion. I'm not here looking for free support. I'm just evaluating
DB2 on Linux and letting whoever cares to know that, as documented,
raw devices don't appear to work as advertised.
Fan Ruo Xin
> on the other hand works wonderfully on raw devices.
Hi, Ronald,
I have to say you are not fair to give us this conclusion. I saw your two messages, the
main reason is because you failed to setup raw device when you use the IBM DB2 UDB. You
think it is so easy to set up it when you use INFORMIX.
For db2, the physical layer of data is container; for Informix, it is chunk. Both support
os file, raw device. From this view, I didn’t find any difference.
Actually, for db2, there are more choice. First no matter where your user data store. You
can store log file on file or raw device. Usually db2 recommends store the system catalog
tablespace and system temporary tablespaces on SMS tablespaces. I mean db2 is more flexible
to configure in this way.
BTW, after I asked a Linux expert how to create a raw device on Linux, it took me one
minute to build my tablespace using raw device. I don’t mean I show off my skill. Just like
it is easy for you to build it using Informix. You have a lot of knowledge of Informix.
When you met some problem, you know how to resolve them, you know where you can get help.
For example, you meet a problem when you initialize your root dbspace/chunk, it is easy for
you to judge if it is because of your Informix configuration or something about raw device
(mode, userid, group, … ) or the other things.
Regards,
Fan Ruo Xin
Ronald Cole
> > raw device support in DB2 isn't ready for prime-time. Informix,
> > on the other hand works wonderfully on raw devices.
>
> Hi, Ronald,
> I have to say you are not fair to give us this conclusion. I saw
> your two messages, the main reason is because you failed to setup
> raw device when you use the IBM DB2 UDB. You think it is so easy to
> set up it when you use INFORMIX.
Ok, to be fair: Informix seems better documented than DB2 in this
regard. I know that I failed to setup DB2 raw devices. I failed even
though I carefully followed IBM's directions in their own
documentation. But even if I succeeded, I believe the resulting
database would have not been secure from other users on the system.
> For db2, the physical layer of data is container; for Informix, it
> is chunk. Both support os file, raw device. From this view, I didn't
> find any difference. Actually, for db2, there are more
> choice. First no matter where your user data store. You can store
> log file on file or raw device. Usually db2 recommends store the
> system catalog tablespace and system temporary tablespaces on SMS
> tablespaces. I mean db2 is more flexible to configure in this way.
>
> BTW, after I asked a Linux expert how to create a raw device on
> Linux, it took me one minute to build my tablespace using raw
> device. I don't mean I show off my skill. Just like it is easy for
> you to build it using Informix. You have a lot of knowledge of
> Informix. When you met some problem, you know how to resolve them,
> you know where you can get help. For example, you meet a problem
> when you initialize your root dbspace/chunk, it is easy for you to
> judge if it is because of your Informix configuration or something
> about raw device (mode, userid, group, … ) or the other things.
I followed the documentation exactly for both products. I didn't find
any errors or omissions in the Informix documentation and as a result
I didn't have any problems with installing IDS and creating a database
on a raw partition. Too bad the same couldn't be said for my foray
into DB2. Perhaps it works for Linux, I don't know. You had to ask
an expert to get it to work for you. To me, that means that the DB2
documentation is inadequate in this area. And I still don't know what
it is I did wrong...
Jeremy Rickard
> For my foray into DB2, the book "The Complete Reference: DB2" by
> Melnyk likewise suggests that you need to create a "tablespace" before
> you can create a "database" (page 159). I even believe I understood
> the difference between a System-Managed Space Table Space and a
> Database-Managed Space Table Space. So, I installed DB2-7.1 PDE and
> fixpaks 3 and 6, carefully followed the "DB2 for Linux HOWTO" and then
> did:
Whatever that book says, you cannot create tablespaces without first
connecting to a database - except with the CREATE DATABASE command,
which supports creation of non-default catalog, user and temporary
tablespaces (in your case on raw devices). Look at the Command
Reference for the relevant syntax.
> Posting an inquiry here garnered no responses whatsoever... even one
> that says "you're wrong". So, I surmised that raw devices on DB2 are
> not a popular option (just like Oracle downplays them) and that is why
> the errors in the documentation weren't caught and why I got no
> response to my post.
Perhaps IBM has downplayed raw devices a bit (I/O throughput not being
an issue for many new systems), but from personal experience they
still remain a popular option with customers (in the UK). There are
many sites that use them with DB2 on various UNIX flavours. As
regards Linux, I don't know - support for raw devices on that platform
was only added very recently - but would be disappointed if it did not
work once you issue the command correctly.
As regards the permissions, I can't think why rw-rw-rw would be
necessary; would have thought rw------- to the DB2 instance owner
would suffice. But since you are having enough problems anyway,
suggest you get everything working first with the original settings
before playing around with the permissions!
Hope this helps a bit.
Jeremy Rickard
United Systems (UK) Ltd.
http://unisystems.biz
+44 (0)20 8688 1588
+44 (0)20 8688 1589 (fax)
Harald Wilhelm
you don't need to connect to a database to create tablespaces. You can create standard tablespaces during database creation. For example:
CREATE DB dbname
ON location
CATALOG TABLESPACE MANAGED BY SYSTEM USING (xxx)
USER TABLESPACE MANAGED BY DATABASE USING (xxx)
TEMPORARY TABLESPACE MANAGED BY SYSTEM USING (xxx);
jric...@unisystems.biz (Jeremy Rickard) wrote on 2002-04-06 00:40:43:
j> Ronald Cole wrote in message news:...
j>
j>
j> > For my foray into DB2, the book "The Complete Reference: DB2" by
j> > Melnyk likewise suggests that you need to create a "tablespace" before
j> > you can create a "database" (page 159). I even believe I understood
j> > the difference between a System-Managed Space Table Space and a
j> > Database-Managed Space Table Space. So, I installed DB2-7.1 PDE and
j> > fixpaks 3 and 6, carefully followed the "DB2 for Linux HOWTO" and then
j> > did:
j>
j> Whatever that book says, you cannot create tablespaces without first
j> connecting to a database - except with the CREATE DATABASE command,
j> which supports creation of non-default catalog, user and temporary
j> tablespaces (in your case on raw devices). Look at the Command
j> Reference for the relevant syntax.
j>
j> > Posting an inquiry here garnered no responses whatsoever... even one
j> > that says "you're wrong". So, I surmised that raw devices on DB2 are
j> > not a popular option (just like Oracle downplays them) and that is why
j> > the errors in the documentation weren't caught and why I got no
j> > response to my post.
j>
j> Perhaps IBM has downplayed raw devices a bit (I/O throughput not being
j> an issue for many new systems), but from personal experience they
j> still remain a popular option with customers (in the UK). There are
j> many sites that use them with DB2 on various UNIX flavours. As
j> regards Linux, I don't know - support for raw devices on that platform
j> was only added very recently - but would be disappointed if it did not
j> work once you issue the command correctly.
j>
j> As regards the permissions, I can't think why rw-rw-rw would be
j> necessary; would have thought rw------- to the DB2 instance owner
j> would suffice. But since you are having enough problems anyway,
j> suggest you get everything working first with the original settings
j> before playing around with the permissions!
j>
j> Hope this helps a bit.
j>
j>
j> Jeremy Rickard
j>
j> United Systems (UK) Ltd.
j> http://unisystems.biz
j> +44 (0)20 8688 1588
j> +44 (0)20 8688 1589 (fax)
Fan Ruo Xin
Ronald Cole wrote:
> Ok, to be fair: Informix seems better documented than DB2 in this
> regard. I know that I failed to setup DB2 raw devices. I failed even
> though I carefully followed IBM's directions in their own
> documentation. But even if I succeeded, I believe the resulting
> database would have not been secure from other users on the system.
Yes, db2 udb configuration parameters are far more than that of Informix.
If you want to learn db2, you have to know what those configuration mean.
It is not easy at beginning, but it has its advantage.
On the other way, from the view of IBM, because there are more and more
customers/partners use LINUX, I think IBM may consider to write a manual
or redbook “db2 udb for linux”. It includes installation and
administration. Even a lot of chapters maybe just copy from those current
manuals. But it is convenient for the customer.
>
>
> I followed the documentation exactly for both products. I didn't find
> any errors or omissions in the Informix documentation and as a result
> I didn't have any problems with installing IDS and creating a database
> on a raw partition. Too bad the same couldn't be said for my foray
> into DB2. Perhaps it works for Linux, I don't know. You had to ask
> an expert to get it to work for you. To me, that means that the DB2
> documentation is inadequate in this area. And I still don't know what
> it is I did wrong...
I asked a Linux expert, because I have no idea about how to create a raw
device on Linux. If you have questions about raw device on AIX, SOLARIS,
HPUX, you search the AIX, SOLARIS, HPUX manuals or DB2, INFORMIX, ORACLE?
Best Regards
Fan Ruo Xin
Fan Ruo Xin
Jeremy Rickard wrote:
> Perhaps IBM has downplayed raw devices a bit (I/O throughput not being
> an issue for many new systems), but from personal experience they
> still remain a popular option with customers (in the UK). There are
> many sites that use them with DB2 on various UNIX flavours. As
> regards Linux, I don't know - support for raw devices on that platform
> was only added very recently - but would be disappointed if it did not
> work once you issue the command correctly.
I don’t think IBM downplayed raw device technology. A lot of IBM benchmarks are based on raw devices. I
remembered there was an article “Informix and raw device. .” which was published on Informix magazine
four or five years ago. Oracle also has similar article. But if this article published today. I do wonder
how many readers are interesting in it. Anyway this is only my opinion.
BTW, I don’t know which particular topic A Log care. DB2 and Raw Device is somewhat big topic. I
remembered there are some paragraphs talking this on “DB2 UDB performance” redbook.
Sorry for my English!
Jeremy Rickard
> Jeremy,
>
> you don't need to connect to a database to create tablespaces. You can create standard tablespaces during database creation. For example:
>
> CREATE DB dbname
> ON location
> CATALOG TABLESPACE MANAGED BY SYSTEM USING (xxx)
> USER TABLESPACE MANAGED BY DATABASE USING (xxx)
> TEMPORARY TABLESPACE MANAGED BY SYSTEM USING (xxx);
>
>
> jric...@unisystems.biz (Jeremy Rickard) wrote on 2002-04-06 00:40:43:
> j> connecting to a database - except with the CREATE DATABASE command,
> j> which supports creation of non-default catalog, user and temporary
> j> tablespaces (in your case on raw devices). Look at the Command
> j> Reference for the relevant syntax.
That *is* what I said (see above), but maybe not as clearly.
:-)
Jeremy
A Log
Redpaper with title "DB2 and Raw Devices"....IBM Can we have one?
A Log.
Ronald Cole
> device on Linux. If you have questions about raw device on AIX, SOLARIS,
> HPUX, you search the AIX, SOLARIS, HPUX manuals or DB2, INFORMIX, ORACLE?
I know all about raw character devices on Linux. Typing "man raw" on
RedHat will get you all the information you need to know to bind one
to a block device. I was able to get Informix IDS up on a raw
character device, so raw character devices aren't my problem.
Ronald Cole
> Ronald Cole <ron...@forte-intl.com> wrote in message news:<m3it75w...@yakisoba.forte-intl.com>...
> connecting to a database - except with the CREATE DATABASE command,
> which supports creation of non-default catalog, user and temporary
> tablespaces (in your case on raw devices). Look at the Command
> Reference for the relevant syntax.
> Hope this helps a bit.
So, in IBM DB2, "database" refers to that which the engine operates on
directly? And therefore the DB2 "create schema" command is the
functional equivalent of Informix's "create database" command? Are
"instances" and "databases" interchangable? If not, then what does a
Venn diagram of DB2's "instances" and "databases" look like? (I'm
assuming most of my problems are "terminology")
Assuming I'm correct so far, it leads me to a few pointed questions
about "best practices" before I start:
* The "ON <path or drive>" clause of the CREATE DATABASE command
doesn't seem to support the syntax to specify the size of the
database, so I assume that "CREATE DATABASE foo ON '/dev/raw/raw2'"
isn't going to work.
* It seems that the CREATE TABLESPACE command refers to the same
table space as in the USER TABLE clause of the CREATE DATABASE
command. Am I on the right track, here? In using the CREATE
DATABASE command to generate the usual DB2-on-raw-device, are the
catalog and temporary table spaces generally left to default to SMS
table space?
Assuming "yes" answers to the above, I'm guessing that:
CREATE DATABASE foo
USER TABLESPACE MANAGED BY DATABASE
USING (DEVICE '/dev/raw/raw2' 263056);
will get me where I want to go. If so, then what exactly is the
difference between the above and:
CREATE DATABASE foo;
CREATE TABLESPACE dms1
MANAGED BY DATABASE
USING (DEVICE '/dev/raw/raw2' 263056);
besides the obvious that the tablespace seems to be given a name in
the latter?
Jeremy Rickard
> So, in IBM DB2, "database" refers to that which the engine operates on
> directly? And therefore the DB2 "create schema" command is the
> functional equivalent of Informix's "create database" command? Are
> "instances" and "databases" interchangable? If not, then what does a
> Venn diagram of DB2's "instances" and "databases" look like? (I'm
> assuming most of my problems are "terminology")
Remember that what "database" means on DB2 UDB For
Windows/UNIX/Linux/OS2, is not what it means on the DB2 UDB For OS/390
flavour. Sticking to the former, the following should serve as a
rough guide...
* An instance is an instance of the DB2 database manager. You can
have multiple instances running on a single machine, if you wish. On
DB2 UDB Enterprise Extended Edition, instances are split across
multiple machines in a cluster.
* An instance can contain many databases. A database belongs to a
single instance.
* A database can contain many schemas (aka schemata). A schema
belongs to a single database.
* A database can contain many tablespaces. A tablespace belongs to a
single database.
* Every database should have at least 3 tablespaces, the default names
of which are SYSCATSPACE, USERSPACE1 and TEMPSPACE1. These are used
to store the database's catalog tables, user tables and temporary
tables respectively.
* When you issue a CREATE DATABASE command, the above 3 tablespaces
are created by default, as SMS tablespaces below the database
directory. You can override these defaults using the extended syntax
mentioned in previous posts.
> * The "ON <path or drive>" clause of the CREATE DATABASE command
> doesn't seem to support the syntax to specify the size of the
> database, so I assume that "CREATE DATABASE foo ON '/dev/raw/raw2'"
> isn't going to work.
* The disk size of your database is effectively determined by the
combined size of all the tablespaces within it (the other space
required is negligible).
Since the default behaviour is to create SMS tablespaces, no fixed
database size is specified - DB2 simply extends the SMS tablespaces as
needs be, until container disk space runs out. If you use the
extended syntax to specify DMS tablespace instead, you must then
specify the size of each container, and hence the total size of the
database. With DMS tablespaces the disk space is preallocated.
As far as I am aware, the database directory cannot be located on raw
devices, but somebody may know otherwise. I cannot think of an
obvious need for this anyway, given the tiny space requirements for
the database directory.
> * It seems that the CREATE TABLESPACE command refers to the same
> table space as in the USER TABLE clause of the CREATE DATABASE
> command. Am I on the right track, here? In using the CREATE
> DATABASE command to generate the usual DB2-on-raw-device, are the
> catalog and temporary table spaces generally left to default to SMS
> table space?
After database creation, you can drop the user tablespace (which drops
any objects you have created in it), and create one or more user
tablespaces with names of your choice.
You can also create other temporary tablespaces and then drop the
original temporary tablespace after creating the new one(s).
SMS is quite often suggested for catalog and temporary tablespaces,
since SMS grows and contracts as needs be. It's your choice though.
> Assuming "yes" answers to the above, I'm guessing that:
> CREATE DATABASE foo
> USER TABLESPACE MANAGED BY DATABASE
> USING (DEVICE '/dev/raw/raw2' 263056);
> will get me where I want to go.
Yes, this will create a database with its database directory under the
default path (specified by the DBDFTPATH database manager
configuration parameter), with SMS catalog and temporary tablespaces,
and a raw device user tablespace. Use the ON clause to override that
default database directory location.
>If so, then what exactly is the
> difference between the above and:
> CREATE DATABASE foo;
> CREATE TABLESPACE dms1
> MANAGED BY DATABASE
> USING (DEVICE '/dev/raw/raw2' 263056);
> besides the obvious that the tablespace seems to be given a name in
> the latter?
The former gives the tablespaces default names. If you also dropped
the original user tablespace, the net result would then be identical
besides the name difference. But if you don't like the default
tablespace, it would be simpler just to use RENAME TABLESPACE.
PGunning
it's use of raw devices. Check out the IBM Redbook, DB2 UDB V7.1
Performance Guideat www.redbooks.ibm.com. I am seeing many customers
goe this way with DMS device containers and I also recommmend it. HTH
Phil
ouRonald Cole <ron...@forte-intl.com> wrote in message news:<m3k7roo...@yakisoba.forte-intl.com>...
Ronald Cole
> After database creation, you can drop the user tablespace (which drops
> any objects you have created in it), and create one or more user
> tablespaces with names of your choice.
>
> You can also create other temporary tablespaces and then drop the
> original temporary tablespace after creating the new one(s).
Ah! That was what I was missing. Thank you for such a cogent
explanation of the terminology! It was most helpful. I have now
created my DB2 database on a raw device under Linux with what I
consider appropriately secure permissions.