Data Stage Designer Performance Tuning in Server jobs

1. In derivations, instead of calling routines, implement the logic in the derivation. This eliminates the overhead of the procedure call.
   
2. Implement the logic in a stage variable and then point the stage variable to the actual field.
   
3. Use Transforms rather than using routines.
   
4. While using the ODBC stage adjust the rows per transaction setting. Try setting to 1000, 5000, or 10000.
   
5. Adjust the array size setting. Try setting to 10, 100, or 1000.
   
6. If output rows are Inserts or Appends and not Updates, consider using a native bulk loader.
   
7. Eliminate unused columns.
   
8. Eliminate unused references.
   
9. Minimize using the stages like SORT, AGGREGATE which minimizes the performance of the job.
   
10. If more transformer Stages are used in sequence in a job, Enable the inter process buffering in the job properties or use the InterProcess Stage between Transformers which improves the performance.
    
11. Direct output to a sequential file compatible with the bulk loader. Then invoke the bulk loader using an after-job subroutine. The bulk loader for Oracle is SQLLDR.
    
12. Avoid using 'like' operator in user defined queries in ODBC stages
    
13. Avoid using stored procedures until and unless the functionality cannot be implemented in Data Stage jobs.
    
14. Tips while creating routines
15. Use variables in the routines.
16. Assign empty values to the variables before using them.
17. Routines will return Ans as return value. Instead of using ANS multiple times, use a variable .Implement the logic in that variable and assign that variable to ANS.
18. For Example: Ans = ''
    If ( Len(Trim(Name)) > 45) Then Ans = Ans : ',' : '24356' End
    Ans = Ans
    The above logic can be implemented using
    ErrStr = ''Ans = ''
    If ( Len(Trim(Name)) > 45) Then ErrStr := ',24356'End Ans = ErrStr
    

Useful Guidelines in Designing Ascential Data Stage Server Jobs

1. Logically create the folders / subfolders on the file server so that the files can be placed and accessed from the relevant folders/subfolders.
   
2. Group the Jobs logically into various categories / Subcategories.
   
3. Comment the Jobs by using Annotation stage which tells the users, the functionality implemented in the job.
   
4. Give descriptions in the properties of the stages used so that others can identify the functionality implemented in it.
   
5. Name passive stages with the Table/File Names they access in it. 6. Name active stages to match their function.
   
6. Name links to express the direction and type of data flowing through them.
   
7. Use job parameters where ever it is required. This makes the process easy while moving into production.
   
8. While using ODBC stages remember to remove the derivations in the columns.
   
9. Use ODBC stage to access relational tables.
   
10. Move constraints from Transform stages to input stage WHERE clauses, to reduce the number of rows the job has to process.
    
11. Use the in-built functions present in Data Stage rather than creating a new routine for implementing the same logic as of in-built function.
    

Easy way to remove the column derivation

1. Open the transformer stage
2. Copy the columns from source(ODBC ) to target(Sequential File)
3. Delete the columns from source(ODBC)
4. Copy all the columns from target to the source.
5. Close the transformer.
6. Now we find that all the derivations are cleared from ODBC stage.

While using Reference Lookups

1. Compare the number of input rows with the number of rows in the reference table. If the reference table is smaller than the number of input rows, pre-load the reference table into a hash file and then reference the hash file.
   
2. Consider moving reference lookups to a join within the input stage. All columns used to join the tables should be indexed to maximize performance.
   
3. If the number of rows in a hashed file is small, consider Pre-loading the file into memory by checking the Pre-load file to memory checkbox in the Hash File stage.
   
4. Remove unused columns from transforms. This does not apply to columns in sequential files or output to hash files.
   
5. While mapping the input records with the Hash Look ups, remember that the fields getting mapped should be of same data type and of same length.
   
6. While loading the hash files, trim the data.

Oracle Certified Professional Exam Questions -Part I

Oracle Certified Professional

The Oracle Certified Professional recognizes achievement in mastering intermediate and advanced Oracle skills. Demonstrate proficiency and receive recognition in managing the implementation of Oracle technology while putting your career on the fast track.

Gain additional job opportunities - fill the Oracle skills gap in the marketplace Help your employers accelerate adoption of technology solutions and maximize ROI Increased opportunity to earn 21% more on average than your non certified counterparts

1. You are creating some tables in your database as part of the logical data model Which of the following constraints have an index associated with them that is generated automatically by Oracle?
   1. Unique
   2. Foreign-key
   3. Check
   4. NOT NULL
2. You have a table with three associated indexes, two triggers, two references to that table from other tables, and a view You issue the DROP TABLE CASCADE CONSTRAINTS statement Which of the following objects will still remain after the statement is issued?
   A The triggers B The indexes C The foreign keys in the other tables D The view
3. In order to set your SQL*Plus session so that your NLS_DATE_FORMAT information is altered in a specific way every time you log into Oracle, what method would be used?
   A Setting preferences in the appropriate menu option B Creating an appropriate LOGINSQL file C Issuing the ALTER USER statement D Issuing the ALTER TABLE statement

4. The EMP_SALARY table has two columns, EMP_USER and SALARY EMP_USER is set to be the same as the Oracle username To support user MARTHA, the salary administrator, you create a view with the following statement: CREATE VIEW EMP_SAL_VW AS SELECT EMP_USER, SALARY FROM EMP_SALARY WHERE EMP_USER <> 'MARTHA'; MARTHA is supposed to be able to view and update anyone in the company's salary except her own through this view Which of the following clauses do you need to add to your view creation statement in order to implement this functionality?

   1. WITH ADMIN OPTION
   2. WITH GRANT OPTION
   3. WITH SECURITY OPTION
   4. WITH CHECK OPTION
5. You are developing PL/SQL code to manipulate and store data in an Oracle table All of the following numeric datatypes in PL/SQL can be stored in an Oracle database, except one Which is it?
   1. CHAR
   2. RAW
   3. DATE
   4. INTEGER
6. You are performing some conversion operations in your PL/SQL programs To convert a date value into a text string, you would use which of the following conversion functions?
   1. CONVERT
   2. TO_CHAR
   3. TO_NUMBER
   4. TO_DATE
7. You have a table called TEST_SCORE that stores test results by student personal ID number, test location, and date the test was taken Tests given in various locations throughout the country are stored in this table A student is not allowed to take a test for 30 days after failing it the first time, and there is a check in the application preventing the student from taking a test twice in 30 days at the same location Recently, it has come to everyone's attention that students are able to circumvent the 30-day rule by taking a test in a different location Which of the following SQL statements would be useful for identifying the students who have done so?
   1. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
   2. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
   3. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
   4. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
8. You create a view with the following statement: CREATE VIEW BASEBALL_TEAM_VW AS SELECT BJERSEY_NUM, BPOSITION, BNAME FROM BASEBALL_TEAM B WHERE BNAME = USER; What will happen when user JONES attempts to SELECT a listing for user SMITH?
   1. The SELECT will receive an error
   2. The SELECT will succeed
   3. The SELECT will receive NO ROWS SELECTED
   4. The SELECT will add data only to BASEBALL_TEAM
9. You query the database with this command: SELECT atomic_weight FROM chart_n WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight BETWEEN (25 AND 75) Which of the following values could the statement retrieve?
   1. 51
   2. 95
   3. 30
   4. 75
10. What will the following operation return? [Choose two] SELECT TO_DATE('01-jan-00') - TO_DATE('01-dec-99') FROM dual;
    1. 365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
    2. A VARCHAR2 value
    3. An error; you can't do this with dates
    4. -36493 if the NLS_DATE_FORMAT is set to the default value
11. What is the purpose of the SUBSTR string function?
    1. To insert a capital letter for each new word in the string
    2. To return a specified substring from the string
    3. To return the number of characters in the string
    4. To substitute a non-null string for any null values returned
12. Evaluate this command: SELECT iisotope, gcalibration FROM chart_n i, gamma_calibrations g WHERE ienergy = genergy; What type of join is the command?
    1. Equijoin
    2. Nonequijoin
    3. Self-join
    4. The statement is not a join query
13. In a SELECT statement, which character is used to pass in a value at runtime?
    1. \
    2. %
    3. &
    4. _ (underscore)
14. Which single-row function could you use to return a specific portion of a character string?
    1. INSTR
    2. SUBSTR
    3. LPAD
    4. LEAST
15. What will the following statement return? SELECT LAST_NAME, FIRST_NAME, START_DATE FROM EMPLOYEES WHERE HIRE_date< Trunc(sysdate) 5;
    1. Employees hired in the past 5 years
    2. Employess hired in the past 5 days
    3. Employees hired more thatn 5 years ago
    4. Employees hired more than 5 days ago
16. Which function(s) accept arguments of any datatype? Select all that apply
    1. SUBSTR
    2. NVL
    3. ROUND
    4. DECODE
    5. SIGN
17. What will be returned from SIGN(ABS(NVL(-32,0)))?
    1. 1
    2. 32
    3. 1
    4. 0
    5. NULL
18. Which functions could you use to strip leading characters from a character string Select two
    1. LTRIM
    2. SUBSTR
    3. RTRIM
    4. INSTR
    5. MOD
19. what will the following query return? SELECT REPLACE(RTRIM('Anticipation','on'), 'ti','shun') from DUAL;
    1. Anticipashun
    2. Anshuncipashun
    3. Anshuncipashunon
    4. Anticipashunon
20. In oracle, what do trigonometric functions operate on?
    1. Degrees
    2. Radians
    3. Gradients
    4. The default is radians, but degrees or gradients can be specified
21. If it is 5 minutes past noon on 15 jan 2000, what will the following statement return? SELECT ROUND(SYSDATE) ROUND(SYSDATE,'Y') FROM DUAL;
    1. 155
    2. 15
    3. 0
    4. 16
22. Which statement about nested functions is most correct?
    1. Single-row nested functions can be nested in either single-row or group functions
    2. Group functions can be nested in other group functions
    3. Group functions cab be nested in single-row functions
    4. A, B and C
    5. A and B only
23. Why will the following query raise an exception? SELECT DEPT_NO, AVG(DISTINCT SALARY), COUNT(JO
24. JOB_COUNT FRIM EMP WHERE MGR LIKE 'J%' OR ABS(SALARY)>10 HAVING COUNT (JO
25. >5 ORDER BY 2 DESC;
    1. A HAVING clause cannot contain a group function
    2. The GROUP BY clause is missing
    3. Abs() is not an oracle function
    4. The query will not raise an exception
26. Why does the following SELECT statement fail? SELECT colorname Colour, MAX(cost) From itemdetail Where upper(colorname) like '%WHITE%' Group by colour Having count(*) > 20;
    1. A GROUP BY clause cannot contain a coloumn alias
    2. The condition COUNT (*) > 20 should be in the WHERE clause
    3. The GROUP BY clause must contain the group functions used in the SELECT list
    4. The HAVING Clause can only contain the group functions used in the SELECT list
27. What will the following query report? SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
    1. The number of employees in each department, including those without a deptnno
    2. The number of employees in each department, ecept those without a deptno
    3. The total number of employees, including those without a deptno
    4. The total number of employees, except those without a deptno
28. Which assertion about the following quires is true> SELECT COUNT(DISTICT mgr), MAX(DISTINCT salary) from emp; SELECT COUNT (ALL mgr), MAX(ALL salary) FROM emp;
    1. They will always return the same numbers in columns 1 and 2
    2. They may return different numbers in column 1 but will always return the same number in column 2
    3. They may return different numbers in column 1 and may return different numbers in column 2
    4. They will always return the same number in column 1 but may return different numbers in column 2
29. What is the limit on the number of values a subquery using the IN operator can return to the parent query?
    1. 1
    2. 32,764
    3. unlimited
    4. 0
30. When using multiple tables to query information, in which clause do you specify the table names?
    1. HAVING
    2. GROUP BY
    3. WHERE
    4. FROM
31. The contents of the CONTESTANTS table are listed as follows: NAME AGE COUNTRY ---------------- -------------- --------------- BERTRAND 24 FRANCE GONZALEZ 29 SPAIN HEINRICH 22 GERMANY TAN 39 CHINA SVENSKY
32. RUSSIA SOO 21 You issue the following query against this table: SELECT NAME FROM CONTESTANT WHERE (COUNTRY, AGE) IN ( SELECT COUNTRY, MIN(AGE) FROM CONTESTANT GROUP BY COUNTRY); What is the result?
    1. SOO
    2. HEINRICH
    3. BERTRAND
    4. GONZALEZ
33. To delete any constraint from the table we have to use command
    1. Drop Constraint
    2. Delet
    3. Alter *
    4. Truncate
34. All the operators are used in single row subquery except one
    1. Between and
    2. <>
    3. =
    4. in
35. All the commands executes in iSQLplus except one
    1. Column
    2. Compute
    3. define
    4. Accept
36. Ascript file which will be executed automatically in iSQLPlus is
    1. afiedtbuf
    2. loginsql
    3. both a and b
    4. none of the above
37. A command in iSQL plus is used to give the status of old and new value of variable
    1. set feedback
    2. set verify
    3. set confirm
    4. none of the above
38. All commands are used to save the changes of the transaction except one
    1. Commit
    2. exitting from sqlplus
    3. DDL command
    4. savepoint
    5. none of the above
39. A Clause which is used in joining two tables other than equality operator is
    1. join
    2. on
    3. in
    4. using
40. A Clause which is the pseudocolumn used to know the current value of the sequence
    1. nextval
    2. current_val
    3. currval
    4. none of the above
41. A Query which is used in top-N analysis is
    1. subquery
    2. correlated subquery
    3. inline query
    4. outer query
42. An operator is used to get and display the redundant records
    1. Union all
    2. Distinct
    3. Union
    4. Intersect
43. All the datatypes with respect to Oracle 9i is true except one
    1. DATE
    2. TIMESTAMP
    3. TIMSTAMP with TIME ZONE
    4. TIMESTAMP WITH LOCAL TIME ZONE
    5. None of the above