Database Technologies

EnterpriseDB Replication tool has been now modified and add new functionality in it. Previous Replication Console, which had been made, was using the DBA Management server and Publication & Subscriptions were dependent. Now, xDB Replication has been divided into two components: 1. Publication Server : (Master Server) 2. Subscription Server: (Slave Server) Some Background of Publication Server. Publication Server has following components: 1. JAVA Based Daemon. 2. MetaDatabase of Publication Server. Publication Server keeps its information (Information about the Primary Database, Tables Details etc.) in the MetaDatabase under schema _edb_replicator_pub Publication server is independent and has no dependency on Subscriptions Server. By default publication server uses the 9011 port. However, if user wants, [S]he can use different ports too. For running Publication Server on different port, use the following command: $EDBHOME/jre/bin/java -Djava.awt.headless=true -jar $

There was a Question put by one of user about loading data in PostgreSQL skipping column which has default value using Copy Command. With above requirement, he has also mentioned that table’s column get changed and default values too. So, he wanted a plpgsql function code, which can be use for any table and file to load the data. Following is a sample plpgsql code which can be use to make things happen: CREATE OR REPLACE FUNCTION Copy_test(text,text,text) returns boolean AS $$ DECLARE rec record; cmd text; colstring text; BEGIN cmd:='select array_to_string(array(select column_name::text from information_schema.columns where table_name='||''''||$2||''''||' and table_schema='||''''||$1||''''||' and column_default is null),'||''''||','||''''||')'; EXECUTE cmd into colstring; cmd:='COPY "'||$1||'&qu

Yesterday, there was a discussion which was going on OTN (Oracle Forum). I was also part of that discussion. Discussion was about the "Does UPDATE statement change rowid in Oracle?" As per my comment of "UPDATE does not change rowid", one of the user has pointed me that UPDATE statement sometime changes the rowid in Oracle and given me following example. SQL> ed Wrote file afiedt.buf 1 create table moving_rowid ( 2 col1 number primary key, 3 col2 number, 4 col3 number, 5 col4 varchar2(10) 6 ) 7* organization index SQL> / Table created. SQL> insert into moving_rowid values( 1, 2, 3, 'foo' ); 1 row created. SQL> insert into moving_rowid values( 2, 3, 4, 'bar' ); 1 row created. SQL> select rowid, col1, col2, col3, col4 2 from moving_rowid; ROWID COL1 COL2 COL3 COL4 ---------------- ---------- ---------- ---------- ---------- *BAEADxsCwQL+ 1 2

Database Technologies: Some trigger and programming feature in PostgreSQL

Anonymous Block Support: Now, user would be able to create anonymous block using plpgsql as given below: do $$ declare id1 varchar; begin select id into id1 from test_sort limit 1; raise notice '%',id1; end; $$ language plpgsql; Output: NOTICE: 1.1 DO Column Trigger: PG9.0 is also supports the column level and When Triggers. Following is an example: create or replace function test_col_trigger() returns trigger as $$ BEGIN RAISE NOTICE 'INSERTED VALUE in Column a %',new.id; RETURN null; END; $$ language plpgsql; create trigger col_trigger after update of "a" on test_def FOR EACH ROW EXECUTE procedure test_col_trigger(); Output: postgres=# update test_def set a='Second'; NOTICE: INSERTED VALUE in Column a 1 UPDATE 1 When Triggers in PG9.0: PG9.0 has "When trigger" which allowed user to create trigger to execute procedure for a particular condition. create or replace function test_col_trigger() returns t

Till Advanced Server 8.3, there was a function pg_get_tabledef, which some people were using to get the definition of table. This function is no more part of new versions of Advanced Server 8.3 (8.3R2AS and 8.4AS). So, they would be missing this function by default. However, they can get the same functionality using following function, which provides similar result as of pg_get_tabledef. Function code is given below: CREATE OR REPLACE Function pg_get_tabledef(text) RETURNS text AS $$ DECLARE tabledef TEXT; dotpos integer; tablename text; schemaname text; prevcol text; coltype text; notnull1 boolean; rec record; oidcheck boolean; BEGIN dotpos:=strpos($1,'.'); if dotpos = 0 then schemaname:='public'; tablename:=substr($1,dotpos+1); else schemaname:=substr($1,1,dotpos-1); tablename:=substr($1,dotpos+1); end if; select relhasoids into oidcheck from pg_class,pg_namespace where pg_class.r

vmstat command: vmstat is a command which can be use to display system statistics. Syntax is given below: vmstat <# second> < #number of times> Example vmstat 1 10 Above Command is to display the system statistics every second, 10 times. Sample Output of the command: vmstat procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 1 0 0 950888 57536 512868 0 0 559 45 479 671 9 2 73 15 Lets understand the column definition: Procs Column would be having three subcolumns r: count (processes waiting for run time) b: count( Processes in uninterruptible sleep) w: Count (Process which are swapped out and runnable) (on RHEL) If any process in b and w column then DBA/System admin has to check the system. Memory Column would be having following subcolumns: swap: swap space currently available.(in kBs) free: Amount of idle memory(kBs) buff: Memory