Wednesday, February 28, 2007

Make a unique constraint deferrable

Is it possible to create an unique deferrable constraint enforced by an unique index? No, it's impossible! You can create an unique constraint with 'deferrable' state. You can create an unique constraint enforced by an unique index. But you can't have both.

Below statement will create an unique constraint enforced by an unique index by default.

21:36:13 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:36:36 2 (charge_element_id, ce_det_version_no, display_order) using index;

Table altered.


This will create an unique constraint with 'deferrable' state enforced by an non-unique index by default.

21:39:29 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:39:40 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index;

Table altered.


You need to drop the constraint and the index separately.


You will get an error when trying to add an unique constraint with 'deferrable' state enforced by an existing unique index.

21:40:14 SQL> create unique index p_pced_uk on p_product_ce_determinants
21:40:44 2 (charge_element_id, ce_det_version_no, display_order);

Index created.

Elapsed: 00:00:00.03
21:40:49 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:40:57 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index p_pced_uk;
alter table p_product_ce_determinants add constraint p_pced_uk unique
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

Elapsed: 00:00:00.06


However, if you add the constraint solely enforced by the unique index, the statement will succeed.

21:41:08 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:41:23 2 (charge_element_id, ce_det_version_no, display_order) using index p_pced_uk;

Table altered.

Elapsed: 00:00:00.01


You still need to drop the constraint and the index separately.


Instead the statement below will succeed and an unique constraint with 'deferrable' state will be created as enforced by an existing non-unique index.

21:42:17 SQL> create index p_pced_uk on p_product_ce_determinants
21:42:28 2 (charge_element_id, ce_det_version_no, display_order);

Index created.

Elapsed: 00:00:00.03
21:42:33 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:42:37 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index p_pced_uk;

Table altered.

Elapsed: 00:00:00.03

I tested in both 9i and 10g. While they have the same behavior, 10g gives more clear messages than 9i.

Put tapes into a tape library

To import a ordinary tape into a tape library using Veritas NetBackup, there are 2 ways. One is to use 'new volumes ...' via 'Media' interface, the other one is to use 'Inventory Robot'. While using 'Inventory Robot', you don't have to set up anything in the 'advanced option', just remember to check 'clear media access port', the library will take care of the rest. To use 'new volumes ...', you need to choose the proper tape type, the slot number, Media ID and so forth. It's important NOT to click on 'Apply' button to take the action. Instead, click on 'Close' button. Otherwise, you will get the error 'The Media ID is not unique in the database'.

To import a cleaning tape into a tape library, seems like 'Inventory Robot' must be used to do so. Also, 'Advanced Option' needs to be accessed to configure sth. You need to add a new rule in 'barcode rule' tab. In the pop window, you need to choose the proper cleaning tape type, e.g. DLT_CLN. You need to give the barcode tag. e.g. CLN (Seems like we don't have to append the barcode number. 'CLN' is enough) At last, a number of the remaining cleanings must be given. Usually it's 25 times. Every time the cleaning tape is used, the number decrements by 1 until 0 automatically. If you did't follow these instructions, even the cleaning tape could be put into the library successfully, the library wouldn't recognize it as a cleaning tape by any means. You will get errors whenever you want to clean a tape drive.

Monday, February 26, 2007

How to get HBA WWNs on Solaris

Recently I want to get the WWNs of the HBAs in our servers to match them with a SAN report generated by a tool in order to know which node in the report represents which server. Basically, there are two methods.

One of them is to run the command below:


# prtpicl -v -c scsi
SUNW,fas (scsi, 1140000032a)
...
:manufacturer JNIC
:fcode_revision Version 3.9
:copyright Copyright (c) 2000-2002 by JNIC
:reg
00 00 00 00 00 40 00 00 00 00 10 00 00 00 00 00 00 40 40 00 00 00 04 00 00 00 00 00 00 40 50 00 00
00 01 00 00 00 00 00 00 00 00 00 00 08 00 00
:emerald_id 0
:sbus_slot 0
:model FCE-1063
:parity-generated
:xilinx_rev 0x2000b
:revision-id 0x4
:device-id 0x1160
:my_wwn_lo 0x69a4966b
:my_wwn_hi 0x100000e0
:lport_cfg 0
:board_id 0x4966b
...

root@boxwood # prtpicl -v -c scsi-fcp
SUNW,qlc (scsi-fcp, 120000003b8)
...
:reg
00 00 10 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 10 10 00 00 00 00 00 00 00 00 00
00 00 00 00 00 01 00 02 00 10 14 00 00 00 00 00 00 00 00 00 00 00 00 00 00 10 00
:node-wwn 20 00 00 e0 8b 1a 90 85
:port-wwn 21 00 00 e0 8b 1a 90 85
:assigned-addresses
81 00 10 10 00 00 00 00 00 00 03 00 00 00 00 00 00 00 01 00 82 00 10 14 00 00 00 00 00 10 00 00 00
00 00 00 00 00 20 00 82 00 10 30 00 00 00 00 00 12 00 00 00 00 00 00 00 02 00 00
...
:manufacturer QLGC
...
:reg
00 00 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 08 10 00 00 00 00 00 00 00 00 00
00 00 00 00 00 01 00 02 00 08 14 00 00 00 00 00 00 00 00 00 00 00 00 00 00 10 00
:node-wwn 20 00 00 e0 8b 1a 55 63
:port-wwn 21 00 00 e0 8b 1a 55 63
:assigned-addresses
81 00 08 10 00 00 00 00 00 00 03 00 00 00 00 00 00 00 01 00 82 00 08 14 00 00 00 00 08 00 00 00 00
00 00 00 00 00 20 00 82 00 08 30 00 00 00 00 08 02 00 00 00 00 00 00 00 02 00 00
:hba0-adapter-hard-loop-ID 0
:hba0-enable-adapter-hard-loop-ID 0
:devfs-path /ssm@0,0/pci@19,700000/SUNW,qlc@1
:driver-name qlc
:binding-name pci1077,2300
:bus-addr 1
:instance 1
:_class scsi-fcp
:name SUNW,qlc

The other method is to use 'prtconf'. Here is an example:

# prtconf -vp > /tmp/prtconf.out
# cd /tmp; vim prtconf.out

Node 0xf00fa738
port-wwn: 210000e0.8b111339
node-wwn: 200000e0.8b111339
compatible: 'pci1077,2200.1077.4082.5' + 'pci1077,2200.1077.4082' + 'pci1077,4082' + 'pci1077,2200' + 'pciclass,010000' + 'pciclass,0100'
interrupts: 00000004
vendor-id: '1077'
device-id: '2200'
revision-id: '5'
subsystem-id: 00004085
subsystem-vendor-id: '1077'
manufacturer: 'QLGC'
reg: 00000002.00030000.00000400.00000002.00030800.00000400
device_type: 'scsi-fcp'
name: 'SUNW,qlc'
version: 'ISP2200 Sbus FC-AL Host Adapter Driver: 1.13.07 09/16/02'


Thursday, February 22, 2007

How to catch an user defined error message

Recently I am working on a project. In this project, there is a database package in which I put all the related functions and procedures. I want a function to generate user defined errors while calling it directly in the hosting environment; meanwhile, I want the calling stored procedure to ignore some of the user generated errors if the function is being called by a stored procedure. The point here is to catch some user defined errors but not the oracle predefined errors.

There is a RAISE_APPLICATION_ERROR statement in the function to generate the user defined error. e.g.

function test
return number
is
e_no_rate_found exception;
begin
raise e_no_rate_found;
exception
when e_no_rate_found then
raise_application_error(-20306, 'No rate found!');
end;

If this function is called directly from sqlplus, the user defined error -20306 will be raised. I want to ignore this error in a procedure which calls this function. Actually, it's same to catch an user defined error as to catch an oracle predefined error. Here we go. In the procedure, we could write the code block below:

declare
e_no_rate_found exception;
pragma exception_init(e_no_rate_found, -20306);
begin
call test;
exception
when e_no_rate_found then
NULL;
end;

The user defined error -20306 is associated with the user defined exception e_no_rate_found using exception_init pragma in the calling procedure.

Solaris utility which can be used to connect to MS SQLServer

Sometimes we need to access MS SQLServer from Solaris platform. In many cases, we need to get the data out of MS SQLServer and import it into Oracle. I wonder if there is any utility on Solaris which can do the similar things to what isql(the interactive SQL client comes with MS SQLServer) can do on Windows platform. Here come two resources:
  • One is called FreeTDS
  • There is one more tools using FreeTDS to connect to MS SQL Server. It's called sqsh