Thursday, February 3, 2011

DS - Useful Links - II

Here you can find some  useful Links


http://www.oracle.com/pls/db102/portal.portal_db?selected=6
http://www.ibm.com/developerworks/data/community/ 
http://www-01.ibm.com/support/docview.wss?uid=swg21413260
http://www.ibm.com/developerworks/data/library/techarticle/dm-0609xu/
http://www.channeldb2.com/profiles/blogs/datawarehouse-project-working
ORACLE : http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_eliminate_duplicates_rows_from_a_table.3F
UNIX :  http://www.panix.com/~elflord/unix/grep.html

http://datastage.org/
http://datastage-tutorials.blogspot.com/2010/04/datastage-overview_24.html
http://datastagecareer.blogspot.com/
http://en.wordpress.com/tag/datastage/
http://www.dsxchange.com/viewtopic.php?p=234616&sid=91f929650228a308564d102d0ca4bedf
http://idpt0000.wordpress.com/2006/09/11/datastage-blog-and-wiki/
http://www.blogtoplist.com/rss/data-warehousing.html
http://datawarehousingsites.blogspot.com/
http://blogs.oracle.com/datawarehousing/
http://blogs.oracle.com/datawarehousing/
http://etl-tools.info/infosphere-datastage-ee/certification.htm
http://www.ibm.com/developerworks/data/library/techarticle/dm-0609xu/
http://www-148.ibm.com/tela/webmail/NlDynamicPage/8205/28501/cosmetic?web_page_id=508702&pre=0
http://www.inmoncif.com/home/
http://www.toadworld.com/BLOGS/tabid/67/Default.aspx
http://obiee101.blogspot.com/search?updated-min=2010-01-01T00%3A00%3A00%2B01%3A00&updated-max=2011-01-01T00%3A00%3A00%2B01%3A00&max-results=50
http://spulagam.blogspot.com/2009/12/logical-versus-physical-database.html
http://www.softpedia.com/get/Others/Home-Education/000-415-IBM-WebSphere-IIS-DataStage-Enterprise-Edition-Practice-Test-Questions.shtml
http://www.globalguideline.com/interview_questions/Questions.php?sc=Extract_transform_load_Oracle_Interview_Questions_Answers

http://etlguru.com/blog/category/etl-testing/


How to control Angryness :::::


http://www.ehow.com/how_2116060_overcome-angriness.html

http://www.soundfeelings.com/free/anger.htm

DS - Lookup Q

When performing a sparse or normal lookup in a parallel job how do you return multiple rows?  
For a sparse lookup all matching rows are returned by default. There is no way to change this and any rows matching the key values will be returned.

For a normal lookup you will need to specify this option:
Right click the lookup stage and click Properties then click the Constraints icon

Select the drop-down list called "return multiple rows from link"

This list will give you all reference links but only one can be used to return multiple rows. 

******************************
Datastage server7.5 installed on Unix machine. need to have a DSN to connect to the database which is on some other unix machine.
let me know it is enough if we create a DSN at the client machine or do we have to change the config file in UNIX?
In UNIX, three files need to be configured.
a) uvodbc.config
b) odbc.ini
c) dsenv
a)uvodbc.config : Here you need to specify the name of data spurce(DSN). Existing configurations in the file can be referred to create new DSN.
For example :
<DSN>
DBMSTYPE = ODBC

b)odbc.ini : Here you need to provide the configuration details of the DSN created in uvodbc.config. The name of data source should be same in uvodbc.config and odbc.ini here the details like like driver location, database name, ip, user id, etc are specified.
Refer the exsting configurations in this file to create new ones.

c)dsenv : It contains the Environment variables needed by the ODBC drivers to connect to the ODBC data source.You need to setup certain env variables to connect to the ODBC data source.
For example :
# Oracle 8i
    ORACLE_HOME=/space/oracle8i
    ORAHOME=/space/oracle8i
    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib;export LD_LIBRARY_PATH
    ORACLE_SID=WSMK5
    ORASID=WSMK5
    export ORACLE_HOME ORAHOME ORACLE_SID ORASID


write a querry to count the goods sold on hourly basis

Select Count(*) DATEPART ( hh sellgoodsdate) From goodssellGroup By DATEPART ( hh sellgoodsdate)

********************

How can I convert a varchar data type into a numeric data type then insert it into a varchar field in a datatabase.

For example, I need to clean up the leading zeros of this string "00000000000009700477" then insert it into a field with charvar data type.

Note that I cannot change the charvar data type to integer or numeric data type in the target table.
Example:
Source (flat file) leading zeros in numeric Data ---> Taget table: dont want leading zeros in varchar field.
00000000000009700477 want to see only 9700477
00000000000000000890 want to see only 890
Is there an interger convert function that can be used in transformer to accomplist this task?
---------
IF LEN(Trim(DSLink9.Field001[159,4],"0",&qu ot;L")) = 0 THEN 0 ELSE Trim(DSLink9.Field001[159,4],"0","L ")

Tuesday, February 1, 2011

Working on a job vs_new...and somehow my connectivity broke..
after that started new session and tried to access the job but getting error that "job is being accessed by other user". How can we resolve? 

So opened Director to cleanup resource...but there is the error as below-
ERROR: Cannot find any process numbers for stages in job vs_new

ANS : The deadlock daemon can be started to release locks and abandoned shared memory segments for processes that are terminated via the UNIX kill command or abnormally due to an unforeseen event.
To start the dslockd process edit $DSHOME/dsdlockd.config and set the start flag to 1.
The daemon will start automatically the next time the DataStage server is started
Enter the time interval, in seconds, that the daemon should wait before rechecking for deadlock conditions. The default is 900 seconds, but you may want to select a smaller interval like 300 seconds.
start=0
timer=900
res=0
log=
         OR   
    
Under the "Locks" you select the "Show All" option, you should still be able to see the job name.
You can either do the "Release All" or "Logout" the corresponding PID.

**********************

99% of the time you will not see those types of locks nor do you see them when you use LIST LOCKS.
The best way to do this and never have to worry about it is to set the deamon value as I described before so it will run and automatically take care of this as well as other important things like lost shared memory segments.

**********************
Data Extraction Rule : The data should be extracted from the previous quarter of the year for the input date passed.
For e.g
If the input date is 14-Apr-2010 the job should extract Q1 data since the input date falls in Q2.
If the input date is 03-Sep-2010 the job should extract Q2 data since the input date fall in Q3.

ANS : Suppose stage is
seqfile --> transformer --> dataset
in transformer stage variables write logic in if then else
if ( juliandayfromdate(datecolumn) < juliandayfromdate(04-01-2011)) then display or extract data columns of first quarter else
if ( juliandayfromdate(datecolumn) < juliandayfromdate(07-01-2011)) then display or extract data columns of second quarter else
if ( juliandayfromdate(datecolumn) < juliandayfromdate(10-01-2011)) display or extract data columns of third quarter
else display or extract data columns of fourth quarter
or simply compare dd mm yy separately in transformer by extracting them and get the data or columns u need.