Systems Engineering and RDBMS

Archive for June 4th, 2007

SQL Server 2008 (Katmai) June CTP is available

Posted by decipherinfosys on June 4, 2007

The June CTP for Katmai (the next version of SQL Server) has been made available by MSFT at their site:

https://connect.microsoft.com/SQLServer

You can get more information on the CTP here.

Posted in News, SQL Server | 1 Comment »

Back to the Basics: IDENTITY INSERT

Posted by decipherinfosys on June 4, 2007

We have received e-mails from some of our readers asking for coverage of some fundamentals of SQL, T-SQL, PL/SQL and SQL/PL which are used in day to day life and they had also been kind enough to list out what they would like to see in the those posts. So, on that note, here is one that deals with the explicit inserts into tables that have identity property for one of the fields. As you know, the Identity property is typically used by database designers when they want to use a surrogate key for a table. This allows them to rely on the database engine to automatically increment the value without specifying a value for that Identity property. In the case of Oracle or DB2 LUW, one can use a Sequence and a before trigger to achieve the same effect. DB2 LUW also supports the Identity property so it has the best of both worlds. At times however, there is a need to explicitly insert a value for that Identity property column i.e. there is a need to over-rule the incremental value that is provided by the database engine. One such scenario might be when you are pulling data from one schema to the other and need to get exactly the same ID values across in order to honor the foreign keys in the system. The other can be when you are doing heterogeneous data migrations like migrating data from say Oracle to SQL Server or from Oracle to DB2. Another scenario could be when you are interfacing with another application and need to preserve the ID values sent by that system.

Under such scenarios, one then needs to enable IDENTITY_INSERT for a given table –> this allows explicit values to be inserted into that table. Example:

CREATE TABLE IDENTITY_DEMO
(
IDENTITY_DEMO_ID INT NOT NULL IDENTITY(1,1),
DEMO_TEXT VARCHAR(10) NOT NULL
)
GO

Now, if you try to insert into that table without setting IDENTITY_INSERT ON, you will get this error:

INSERT INTO IDENTITY_DEMO (IDENTITY_DEMO_ID, DEMO_TEXT) VALUES (1, ‘TEST’);

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘IDENTITY_DEMO’ when IDENTITY_INSERT is set to OFF.

In order to be able to do an explicit Identity Insert, you will need to SET the Identity Insert for that table:

SET IDENTITY_INSERT IDENTITY_DEMO ON

INSERT INTO IDENTITY_DEMO (IDENTITY_DEMO_ID, DEMO_TEXT) VALUES (1, ‘TEST’);

(1 row(s) affected)

Once done, you can set the IDENTITY_INSERT to OFF:

SET IDENTITY_INSERT IDENTITY_DEMO OFF

Please remember that at any time, only one table per session can have this property set ON. If you try to do it for another table in the same session, the engine will report that as an error and will list out the table that has that property turned on. Also note that say if the current Identity value for the table is 1000 and the value that you have now inserted is say 2000, this will re-seed the identity value for that table to be 2000. Example:

select ident_current(‘IDENTITY_DEMO’)

—————————————
1000
SET IDENTITY_INSERT IDENTITY_DEMO ON
INSERT INTO IDENTITY_DEMO (IDENTITY_DEMO_ID, DEMO_TEXT) VALUES (2000, ‘TEST’);
SET IDENTITY_INSERT IDENTITY_DEMO OFF
GO

select ident_current(‘IDENTITY_DEMO’)

—————————————
2000

The IDENT_CURRENT() function shown above can be used for getting the current set Identity value for that table. Other such functions that you might find useful are: IDENT_INCR() (to get the increment value – in our example it is set to 1 in the definition itself), IDENT_SEED() (to get the seed value). Per table, you can have only one identity column so these functions take n just the name of the table as the argument and give out the value as per their definitions as mentioned above.

If there are other topics that you would like covered on this blog, please send an e-mail to: info@decipherinfosys.com and we will include those topics in our future blog posts. Also, if you get some time, please provide us with a feedback on the current posts.

Posted in DB2 LUW, SQL Server | 2 Comments »

Configuring RedHat Linux for Oracle 10gR2 client installation

Posted by decipherinfosys on June 4, 2007

Here is a blog post detailing how to configure an environment on a RedHat Linux OS for the installation of a 10gR2 Oracle Client. It assumes some familiarity with both RHEL and Oracle.

These are the steps:

1) Create the Oracle user and necessary groups:

a. Log into the machine as root.

i. Either telnet as a lesser user and su to root, or use putty or some other ssh connection.

b. Before we can create the user, we must first create the groups that we are going to assign the user to. We need two groups for the installation: oinstall and dba. Although it is not required for the installation, we try to use the same group and userid’s across platforms. For the Oracle user we want to use 501, oinstall – 300, dba – 500. It is very possible these userid’s are not available (ie used by another group). If this cannot be changed, then you can pick another id, or have the system do it for you. The purpose of this is to make file sharing simpler when mounting volumes for other machines. The –g option indicates you’re a going to manually specify the group id. Below are the commands to create the necessary groups.

i. groupadd -g 300 oinstall
ii. groupadd –g 500 dba

c. Create the Oracle user (You can also use a GUI tool for this, if available). For the user, we want the id to be 501. Again another can be used. The common id convention is for convenience. Below is the command to create the oracle user.

i. useradd -u 501 -g oinstall -G dba -d /home/oracle -s /bin/ksh oracle

1. –u sets the userid to 500
2. –g specified the primary group to be oinstall
3. –G specifies the dba as an additional group oracle will belong to. This can be a comma delimited list of groups.
4. –d specifies /home/oracle to be the user’s system home directory. It will be created automatically.
5. –s specifies the default shell for the oracle user to be /bin/ksh. This is the korn shell.

ii. This will create the user oracle home, but the group for the oracle home will be oinstall. This should be changed as it has nothing to do with the installation. Go to the /home directory and execute the following:
chgrp -R dba oracle
The /home/oracle directory is a directory where DBA’s can keep scripts and such.
iii. The password for the oracle user needs to be set. Use the passwd command to do this:
passwd oracle
You will be prompted to enter the password and then confirm.

iv. In korn shell, the user’s environment is initialize via settings in the “.profile” file. The two attachments below can be used to initialize the environments. The ‘.’ in front of the file name indicates that the file is hidden. It is not a typo.

1. “.profile” This will be the initializing file. For oracle, our convention is to keep this file fairly simple. Since Oracle software can have multiple “Oracle” (not system) homes, we create additional “.” environment files that allow DBA’s to easily change the environment for specific homes. This will become clearer during the oracle client installation instruction.
export PATH=.:$PATH:/sbin

# set up the terminal:
stty istrip
stty erase “^?” kill “^U” intr “^C” eof “^D”

TERM=xterm
export TERM
# Set up the shell environment:
set -u
trap “echo ‘logout'” 0

# Set up the shell variables:
EDITOR=vi
export EDITOR

set -o vi

#. ./.oracle92RACenv
. ./.oracle10gRACenv

2. “.” environment file can be call what ever is appropriate. In this case, we will call it “.oracle10gR2ClientEnv”. This will be more detailed in the environmental variables. It will contain the settings for a specific “Oracle” Home. There are two important values in this file, that will need to be changed. That is the ORACLE_HOME value and the ORACLE_BASE value. Where ever the ORACLE_HOME is for this installation, that full path will need to be specified. Same for the ORACLE_BASE. These will be defined in the following steps. You will need to revisit this file to ensure it is configured properly.

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:${HOME}/bin:/usr/bin/X11:/sbin:.;export PATH
umask 022
set -o vi
alias ll=’ls -ltr’

ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE
ORACLE_HOME=${ORACLE_BASE}/product/Ora102;export ORACLE_HOME

NLS_LANG=AMERICAN_AMERICA.AL32UTF8;export NLS_LANG

ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data;export ORA_NLS33

PATH=${ORACLE_HOME}/bin:/usr/ccs/bin:/usr/local/bin:/usr/vacpp/bin:/opt/IBMJava2-131/bin:/opt/IBMJava2-131/jre/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin; export PATH
LIBPATH=${ORACLE_HOME}/lib;export LIBPATH
CLASSPATH=${ORACLE_HOME}/JRE/lib:${ORACLE_HOME}/jlib:${ORACLE_HOME}/rdbms/jlib:${ORACLE_HOME}/network/jlib;export CLASSPATH
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORACLE_HOME}/oracm/lib;export LD_LIBRARY_PATH

TMPDIR=/u01/tmp;export TMPDIR

MACHINE_NAME=`hostname -s`;export MACHINE_NAME
PS1=’10g_${ORACLE_SID}_${MACHINE_NAME} ${PWD}=> ‘;export PS1

2) Create the installation directories for the oracle binaries. For this example /u01 will be used as the base volume.

a. As root create an “app” directory off of /u01.
b. Change the owner and group of the “app” directory as follows:
chown oracle:dba app
c. As oracle, Create the following path: “/u01/app/oracle/product/Ora102” This is the value needed for the ORACLE_HOME parameter in the environment file.
At this point, the group associated with the files and directories above “app” should be oinstall. This will be done by default as you create the directories.
d. In the environment file, set the ORACLE_BASE value to “/u01/app/oracle”.
set the ORACLE_HOME value to “${ORACLE_BASE}/ product/Ora102”.
e. Once these are set correctly, re-initialize by executing “. ./.profile”.
f. At the command prompt, confirm the settings by using the following example: (note* the bolded values should be correct)

10gR2Client_RHBLD01 /home/oracle=> . ./.profile
10gR2Client_RHBLD01 /home/oracle=>
10gR2Client_RHBLD01 /home/oracle=> echo $ORACLE_HOME
/u01/app/oracle/product/Ora102
10gR2Client_RHBLD01 /home/oracle=> echo $ORACLE_BASE
/u01/app/oracle
10gR2Client_RHBLD01 /home/oracle=> cd $ORACLE_HOME
10gR2Client_RHBLD01 /u01/app/oracle/product/Ora102=> cd $ORACLE_BASE
10gR2Client_RHBLD01 /u01/app/oracle=>

g. Create the temporary directory that is specified by TMPDIR in the “.” environment file. Do not forget to do this. The client installation may continue all the way through with out error, but when you attempt to execute sqlplus and connect to a database, you will get errors.
Change the owner ship of the temporary directory using: “chown oracle:dba tmp”
Validate by: cd $TMPDIR. This should take you to the temporary directory specified.

3) Pre-Installation requirements:

a. Kernel version: 2.6.9-5.EL

10gR2Client_RHBLD01 /u01/Installation_Media/client=> uname -a
Linux RHBLD01 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux

b. Required packages(version must be these or higher):

binutils-2.15.92.0.2-13.EL4
compat-db-4.1.25-9
compat-libstdc++-296-2.96-132.7.2
control-center-2.8.0-12
gcc-3.4.3-22.1.EL4
gcc-c++-3.4.3-22.1.EL44
glibc-2.3.4-2.9
glibc-common-2.3.4-2.9
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.3-22.1
libstdc++-devel-3.4.3-22.1
make-3.80-5
pdksh-5.2.14-30
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.2
setarch-1.6-1

If not installed, then install. If not the appropriate version, then upgrade the component.
The packages can be checked for their version by executing the following:

rpm –q <package_name>

example(installed):
10gR2Client_RHBLD01 /u01/Installation_Media/client=> rpm -q binutils
binutils-2.15.92.0.2-10.EL4

example(not installed):
10gR2Client_RHBLD01 /u01/Installation_Media/client=> rpm -q xscreensaveer
package xscreensaveer is not installed

There are several ways to update/install the packages:

1) manually update/install the packages using rpm. (becomes painful with dependencies)
2) use up2date to get the latest version of the packages. Up2date handles dependencies for you by installing or upgrading dependent packages.
3) Use the up2data gui if available.

4) In order to perform the installation, you will need to have an xserver (Exceed) running on your local machine or you will have to go directly to the console, which will need a gui interface. I will give instruction for the remote connection using an xserver.

a. telnet to the machine as oracle.
b. Go to the directory where you have unpacked the installation software.
c. Execute “export DISPLAY=<your ipaddress>:0.0” This is case sensitive.
d. Start the xserver on your machine in passive mode.
e. Execute ./runInstaller
f. The gui installation should appear on your machine for the Oracle client.

5) You are now ready to begin the client installation process. Please refer to the installing Oracle 10gR2 client installation document for more details. You can also refer our previous blog post for it.

Posted in Linux, Oracle | Leave a Comment »

Bill Gates and Steve Jobs Interview

Posted by decipherinfosys on June 4, 2007

All Things Digital has posted the transcript of the interview that they conducted at the D5 conference this year. Pretty interesting read – you can read more on that here.

Posted in News, Technology | Leave a Comment »