Archive

Archive for the ‘Uncategorized’ Category

Vertica Parallel Load

As it is known there are 2 types of storage area in Vertica, WOS and ROS.  Whereas WOS stores data in-memory, ROS stores data in-disk. If you are using COPY command without DIRECT hint, data is first loaded into the WOS area then Tuple Mover moves the WOS data to the ROS area. If you load big files, streams you can pass the WOS area by using DIRECT command and it improves the load performance.

Actually I do not want to mention about the DIRECT hint, I want to talk about the PARALLEL file load.

Think about you have a big file, more than 100 GB size and you want to load this file data into the vertica as parallel as possible. Where can you parallelise at ? File reading parallelism and loading parallelism.

With loading-parallelism you can modify the resource-pool configuration to increase or decrease # of slave threads by using EXECUTIONPARALLELISM parameter. If you set this value to the 1 just one thread executes the query causes to reduce the response times if you have multiple small queries. But it causes to long response times for long queries. For these types of queries you should increase the parameter value as well.

Another method is the main subject of this post, file reading parallelism. If you have multiple machine and one big file and you want to read and load this file to the vertica database in default you can use just one machine to read file.

COPY customers FROM LOCAL
'/data/customer.dat'
DELIMITER '~'
REJECTMAX 1000
EXCEPTIONS '/home/dbadmin/ej'
REJECTED DATA '/home/dbadmin/rj'
ENFORCELENGTH
DIRECT
STREAM NAME 'stream_customers';

But if you have 4 machine and if you split-up that file into 4 pieces ( Linux Split, then distribute files across the nodes ) then you can load much faster than the previous method. With this method you should also specify the node name.

COPY customers FROM 
'/data/customer.dat' ON v_myv_node0001,
'/data/customer.dat' ON v_myv_node0002,
'/data/customer.dat' ON v_myv_node0003,
'/data/customer.dat' ON v_myv_node0004
DELIMITER '~'
REJECTMAX 1000
EXCEPTIONS '/home/dbadmin/ej'
REJECTED DATA '/home/dbadmin/rj'
ENFORCELENGTH
DIRECT
STREAM NAME 'stream_customers';

And also I experienced that if you put multiple files for each node it can load much much faster than the all methods. For example in above case we have 100 GB customer file and we split it into 4 pieces and then each file has 25 GB data. In addition to that if you split it again in each node and issue seperate COPY commands you can see the difference.

-- COPY STREAM 1
COPY customers FROM 
'/data/customer1.dat' ON v_myv_node0001,
'/data/customer1.dat' ON v_myv_node0002,
'/data/customer1.dat' ON v_myv_node0003,
'/data/customer1.dat' ON v_myv_node0004
DELIMITER '~'
REJECTMAX 1000
EXCEPTIONS '/home/dbadmin/ej'
REJECTED DATA '/home/dbadmin/rj'
ENFORCELENGTH
DIRECT
STREAM NAME 'stream_customers';

-- COPY STREAM 2
COPY customers FROM 
'/data/customer2.dat' ON v_myv_node0001,
'/data/customer2.dat' ON v_myv_node0002,
'/data/customer2.dat' ON v_myv_node0003,
'/data/customer2.dat' ON v_myv_node0004
DELIMITER '~'
REJECTMAX 1000
EXCEPTIONS '/home/dbadmin/ej'
REJECTED DATA '/home/dbadmin/rj'
ENFORCELENGTH
DIRECT
STREAM NAME 'stream_customers';
Advertisements
Categories: Uncategorized Tags: , ,

The Book: CentOS System Administration Essentials

Packt Publishing is the UK based tech book publishers which published the book in recently. I am proud of being one of the reviewer of this book.

CentOS System Administration Essentials

CentOS System Administration Essentials

This book is a guide for administrators and developers who develops application which run in CentOS. It covers many subjects that administrators and developers need to know work with Linux. And also it has some bonus topics like installation of LDAP, Nginx, Puppet etc.

The book is not useful the people who is the Linux newbie. There are many other books can be read for that purpose.

Besides, this book is my second book which I am the one of the reviewer. The first one is the Getting Started with Oracle Event Processing 11g.

Categories: Uncategorized

Using Variable Based Hints in an ODI Knowledge Module

February 15, 2013 1 comment

In an ODI interface we know that a knowledge module should be chosen. This knowledge module lines up the steps which are executed in order. And also we can add “Options” and “Checks” to make a knowledge module more generic. For more information click here to view documentation . In the knowledge module steps we have been using Oracle sql statements and and also sql hints has been used. How can we implement the hints in the sql statements which are used in the knowledge module?

Since many interfaces use the same knowledge module, it should be generic.

If we add hints directly into the knowledge module as in the below we should prepare a new knowledge module for each interface and as you’ve predicted this method a little bit creepy.

pic1

Instead, we can add KM options to that knowledge module as my mentor have explained in his blog, http://gurcanorhan.wordpress.com/2013/02/05/adding-hints-in-odi/ .

That method is really useful when you need to use SQL hints in your sql statements. But we thought that we can enhance a little bit more to make this method more practical. Consider that if we want to change the hint option value such as increase the parallel degree of  select statement, we should change the option in the interface. Even if your production environment has just execution repository, you could not do that.

Gurcan and me  thought about it and developed a new method to make the parallelism more generic. In a database table we will store the hints of each interface. Consider a knowledge module, its name is “INCREMENTAL UPDATE” and it has 10 steps, totally 15 dml statements. Each dml statements contains hint clauses.

This kind of database table like this:

pic2

For example the TEST_INTERFACE interface uses the “INCREMENTAL UPDATE” knowledge module and this knowledge module has 9 dml statements which can contain hint clause.

The problem is how to access this database table and how to use hint clauses in knowledge module steps.

We know how to access the database in a knowledge module step but how to assign hint clauses which are in the database to java or jython variables.

Step1

Add a step into knowledge module to get the hint clauses from the database. Name the step as “Get Hiints”.

pic3

Step2

Select “Command on Source” tab and in the text area we should write the sql statement to get hint clauses.:

pic4

The sql statement which I wrote seem a little bit complex  but it is not. The point is you should get just ONE ROW with these sql statement however there may be multiple columns it is worthless. In fact, the sql query should return like this row:

pic5

The other important part is we use the

snpRef.getStep("STEP_NAME")

method to use interface’s name, we need to this clause because just before when an interface starts to run hints read from the database table by using interface name.

Step3

After you’ve completed the query in the source tab, select the target tab and configure the java variables as shown in the below:

pic6

We define 9 java variables and each of them contains a hint clause, they are read from the database and assign to the java variables. Pay attention here,

String insertFlow_insertHint=”#HINT_1”;

It means that read the “HINT_1” field (column) of the sql query and assign it to the “insertFlow_insertHint” java variable. Since there is a sql column to java variable assignation, the sql query should not return more than one row.

Step4

Now, we have the hint clauses in the java variables the last point is how to use java bean variables in a sql statements.

pic7

In a knowledge module step you can use the java variables as shown in the above.

In this blog post I tried to tell how to use variable based sql hints in the ODI knowledge modules. Let’s discuss the things which can be more generic!

TROUG – Coherence Presentation and Demo Video

October 12, 2012 2 comments

Yesterday, I attended Turkish Oracle User Group ( TROUG ) event as a speaker in Bahcesehir University, Istanbul. I talked about cache concept, why do we need to cache and a few Oracle Coherence features. And also I mentioned Coherence Cache topologies such as Replicated Cache, Distributed Cache and Near Cache and also Write,Read Through, Write Behind Queue and Refresh-Ahead mechanisms.

It was delighted but the end of the my presentation I was disappointed with my VirtualBox. When I wanted to make a demo in my Eclipse which runs inside the OEL 5 in VirtualBOX 4, there was something wrong. Some of my Eclipse Launch Configuration have been disappeared in “Run As” section so there was lots of configuration and I lost all of them.

Fortunately, I decided to upload a video which tells the some of Oracle Coherence features and CohQL examples inside the VirtualBox.

You can watch this video in vimeo:

https://vimeo.com/51314443

Here is the my presentation.

I’m attending TROUG ( TuRkish Oracle User Group ) event as a speaker

September 14, 2012 Leave a comment

I will be attending TROUG Event as a speaker on 12 October in Bahcesehir University, Besiktas. There are two parallel sessions during the day and one of them for DBAs and the other one for Oracle Developers. I will be speaking about Oracle Advanced Caching Mechanism in fact Oracle Coherence. 

http://www.troug.org/?p=278

Chameleon Logo

Categories: Uncategorized

UKOUG Conference 2010

October 17, 2010 3 comments

We developed an Oracle CEP application which processes GSM network events and produces alerts. We also used Oracle Coherence to facilitate handling huge data.
My presentation about this topic has been selected for inclusion within the this year programme to present on Tuesday 30th November:

Processing Turkcell GSM Network Data with Oracle CEP
(30/11/2010 13:45 – 14:30)

I’m a bit excited because this will be my first visit to England and my first presentation in abroad 🙂

Categories: Uncategorized

Installing jQuery to Application Express ( APEX )

Sometimes, you want to add different things your page such as different buttons, images, motions. JQuery API provides these wishes by using JavaScript and CSS.

For detail information, examples and to do download this api, visit http://jquery.com.

If you want to import and use this api in your Apex Application you will have to do some steps.

1 ) Download latest version of jQuery Api from http://jquery.com or customizable version at : http://jqueryui.com/download
2 ) Install those files to $ORACLE_HOME/apex/images/jquery/ folder. ( jQuery folder must be created ).
3 ) Then, you should edit the $ORACLE_HOME/apex/images/imagelist.xml file.
This file an xml file and you should add those under the directories node.



jquery
jquery/theme
jquery/theme/images

And, you should add file paths under the files node. ( Those file names should be changed if you downloaded another version instead of 1.6 Personalized JQuery )

/jquery/jquery-1.3.1.js
/jquery/jquery-ui-personalized-1.6rc6.min.js
/jquery/jquery-ui-personalized-1.6rc6.js
/jquery/theme/ui.accordion.css
/jquery/theme/ui.all.css
/jquery/theme/ui.base.css
/jquery/theme/ui.core.css
/jquery/theme/ui.datepicker.css
/jquery/theme/ui.dialog.css
/jquery/theme/ui.progressbar.css
/jquery/theme/ui.resizable.css
/jquery/theme/ui.slider.css
/jquery/theme/ui.tabs.css
/jquery/theme/ui.theme.css
/jquery/theme/images/ui-bg_diagonals-thick_18_b81900_40x40.png
/jquery/theme/images/ui-bg_diagonals-thick_20_666666_40x40.png
/jquery/theme/images/ui-bg_flat_10_000000_40x100.png
/jquery/theme/images/ui-bg_glass_65_ffffff_1x400.png
/jquery/theme/images/ui-bg_glass_100_f6f6f6_1x400.png
/jquery/theme/images/ui-bg_glass_100_fdf5ce_1x400.png
/jquery/theme/images/ui-bg_gloss-wave_35_f6a828_500x100.png
/jquery/theme/images/ui-bg_highlight-soft_75_ffe45c_1x100.png
/jquery/theme/images/ui-bg_highlight-soft_100_eeeeee_1x100.png
/jquery/theme/images/ui-icons_228ef1_256x240.png
/jquery/theme/images/ui-icons_222222_256x240.png
/jquery/theme/images/ui-icons_ef8c08_256x240.png
/jquery/theme/images/ui-icons_ffd27a_256x240.png
/jquery/theme/images/ui-icons_ffffff_256x240.png

4 ) After that, you should run apex_epg_config.sql ( in $ORACLE_HOME/apex directory ) and enter $ORACLE_HOME whichever it is, to refresh file paths.

SQL> @apex_epg_config

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Enter value for 1: D:\app\TTASUNGUR\product\11.1.0\db_1\
old 1: create directory APEX_IMAGES as ‘&1/apex/images’
new 1: create directory APEX_IMAGES as ‘D:\app\TTASUNGUR\product\11.1.0\db_1\/
apex/images’

Directory created.

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:00:59.68

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Session altered.

Directory dropped.

5 ) So far, you imported and installed the jquery to your Apex environment. Now, you can use jquery in your apex pages. Let’s do an example, create a page and create a region and we will create a draggable div place.

$(document).ready(function()
{
$(“#tasinabilir”).draggable( { revert: true } );

}
);

Bura tasinabilir bir bolge

( There are many examples at http://jquery.com )
6 ) Then test your page, click div place and drag it to anywhere and release, it will return its first position.