Thursday, May 27, 2010

Convert PNG to GD2 for nagios

When working with nagios and working with new icons to show on your status map you might encounter a filetype called GD2. In most cases you will have a file, for example LINUX_SERVER.png and you will also have a file called LINUX_SERVER.gd2 .


The .png files are used to be displayed in your browser wherever they are needed. However the status maps where you can see all your server in on screen on a map is generated. For the generation of this map the nagios code is reading the images needed and for a more direct access to the image source the gd2 files are used. GD2 is coming from the GD Graphics Library.

In some cases you download a icon set which is not containing gd2 files but only png files. In this case you can make use of the pngtogd2 utility. This utility is (for debian) part of the libgd-tools package and can be installed via apt-get.

When using the pngtogd2 utiltiy please make sure you use the uncompressed option and chunck size of 0. Compressed gd2 files will make rendering the status map slower so if you do not really need to use compression than don’t use it.

Tuesday, May 25, 2010

Oracle database oerr

In most cases when a error is happening in Oracle you will get a ora-###### message and some text. However in some cases you will only receive the ORA error message number from Oracle and not the text. Most likely you would like to know the meaning of the error message. For this reason Oracle databases are shipping (for the Linux installations) with a tool named oerr which can be found in $ORACLE_HOME/bin when you for example want to know the meaning of the error ORA-06512 you can execute the command "oerr ora 06512" which will provide you the explaining text for this error message.

In case you run your Oracle database on a windows machine and you would like to have access to this tool, Yong Huang has created a perl script to run on your windows machine which can do the same and can be downloaded from http://orafaq.com/scripts/perl/oerr.txt .

Most likely you will not have to use the option very often however it can be very handy from time to time.

Saturday, May 01, 2010

Oracle SQL prevent unique constrain violated error

When creating a database model in your Oracle database (or any other database) you will have to creation relations. When you do things correct (in my opinion) you will base those relations on primary and foreign keys and by using those building unique constraints. Or simply put, binding tables on values. If you want to build a one to many relation you will have to make sure that you hold a column with a unique value, this will be your primary key which can not be null. Making sure that the value in this column is unique is done by the Oracle database, if you try to insert a value which is not unique the database will throw a unique constrain violated error. So you will need to provide some mechanism to ensure you only insert unique values.

So, the database will prevent you from inserting a non unique value and will throw a error message, to prevent your code from entering a couple of options possible. For example one can query the highest value + 1. The downside from this is that you need to query the table before you can do a insert which results in computation which is not needed and which can be intense on large tables. a better way is to make use of a database sequence which will provide you the next value. When using a sequence you can do the insert in a couple of ways.

You can get the next value from the sequence like:
SELECT 
sequencename.nextval 
FROM 
dual;

you could use this as a sub-query in your insert statement or get it in your pl/sql code as a variable and use this variable in your insert statement. All options are valid and can be used in your code however an other option is available which I prefer. This option has to be considered the moment you create the table and will make use of a trigger and sequence. The trigger will fire when you insert a record into the table without the unique value. The unique value will be requested by the trigger and inserted into the table.

Before creating the table you will have to define a sequence. In the example below we first create a sequence named johan.WEBLOG_EXAMPLE_0_SEQ

CREATE SEQUENCE "JOHAN"."WEBLOG_EXAMPLE_0_SEQ"
MINVALUE 1 
MAXVALUE 9999999999999999999999999999 
INCREMENT BY 1 
START WITH 1 
CACHE 20 
NOORDER 
NOCYCLE ;
This sequence will provide us a unique number every time we do a request for a nextval like we did in the first example and in the example below where it is done manual:
SELECT 
johan.WEBLOG_EXAMPLE_0_SEQ.nextval 
FROM 
dual;
Now we have created a sequence we will create the table in which we will place a trigger which uses the sequence johan.WEBLOG_EXAMPLE_0_SEQ. We name the table WEBLOG_EXAMPLE_0
CREATE TABLE "JOHAN"."WEBLOG_EXAMPLE_0"
(
"ID_COLUMN"     NUMBER NOT NULL ENABLE,
"DATA_COLUMN_0" VARCHAR2(120 BYTE),
"DATA_COLUMN_1" VARCHAR2(120 BYTE),
CONSTRAINT "WEBLOG_EXAMPLE_0_PK" PRIMARY KEY ("ID_COLUMN") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE
)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "USERS" ;
CREATE OR REPLACE TRIGGER "JOHAN"."WEBLOG_EXAMPLE_0_TRG" BEFORE
INSERT ON JOHAN.WEBLOG_EXAMPLE_0 FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF :NEW.ID_COLUMN IS NULL THEN
SELECT WEBLOG_EXAMPLE_0_SEQ.NEXTVAL INTO :NEW.ID_COLUMN FROM DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "JOHAN"."WEBLOG_EXAMPLE_0_TRG" ENABLE;
In the example above you can see that we create the table and also create a before trigger named WEBLOG_EXAMPLE_0_TRG. This will make sure that before the record is inserted it will be checked if a value for the unique column "ID_COLUMN" is given. If this value is NULL it will request a nextval from the sequence and place this in the "ID_COLUMN". after this is done the record is inserted. This is one the most secure ways of making sure you will try to insert a unique value in your "ID_COLUMN". In the example below we will see this:
INSERT INTO JOHAN.WEBLOG_EXAMPLE_0 
(DATA_COLUMN_0, 
DATA_COLUMN_1) 
VALUES ('blabla', 
'more blabla');
when we do a select on the table we will notice that the trigger is working and that the "ID_COLUMN" column now has a value of 1, the following will get a value of 2 etc etc etc.