Teradata - Using Roles

Hi Friends,

Today we will learn using roles in teradata with examples:

Create roles.

 

  CREATE ROLE Inquiry_HR;

  CREATE ROLE Update_HR;

  CREATE ROLE Inquiry_Payroll;

  CREATE ROLE Update_Payroll;

  CREATE ROLE Batch_HR_Pay;

 

Assign access rights to the roles (partial listing).

 

  GRANT SELECT, EXECUTE   ON HR_VM TO Inquiry_HR;

  GRANT INSERT, UPDATE, DELETE   ON HR_VM TO Update_HR;

 

Grant users permission to use the roles.

 

  GRANT Inquiry_HR   TO Update_HR;   /*nested role*/

  GRANT Inquiry_HR   TO Emp01, Emp02;

  GRANT Update_HR   TO Emp03, Emp04;

  GRANT Update_HR   TO Sup05 WITH ADMIN OPTION;

  GRANT Batch_HR_Pay   TO Sup05 WITH ADMIN OPTION;

 

Modify the user to set the default role.

  MODIFY USER Emp01 AS DEFAULT ROLE = Inquiry_HR;

  MODIFY USER Emp02 AS DEFAULT ROLE = Inquiry_HR;

  MODIFY USER Emp03 AS DEFAULT ROLE = Update_HR;

  MODIFY USER Emp04 AS DEFAULT ROLE = Update_HR;

  MODIFY USER Sup05  AS DEFAULT ROLE = Update_HR;

Emp01 –   is granted to Inquiry_HR role;

Inquiry_HR is current role.

 

  SELECT   * FROM Employee_v  ORDER BY   1;  (success)
 

  UPDATE   Employee_v

  SET   Dept_Number=1001

  WHERE   Employee_Number=100001;  (fails)

  Why does this statement fail for Emp01?

Emp03 –   is granted to Update_HR role;

Update_HR is current role.

  SELECT   * FROM Employee_v ORDER BY   1;  (success)

 

  UPDATE   Employee_v

  SET   Dept_Number=1001

  WHERE   Employee_Number=100001;  (success)

  Why do both of these statements succeed for Emp03?

 

Sup05  –   is granted to Update_HR role WITH ADMIN OPTION.

  GRANT Update_HR TO Emp02;  (success)
 

Emp02 –   is granted to Update_HR role; Inquiry_HR is current role.

  SELECT   * FROM Employee_v ORDER BY 1;  (success)

  UPDATE   Employee_v

  SET   Dept_Number=1001

  WHERE   Employee_Number=100001;  (fails)

  Why does this statement fail for Emp02?

Emp02 – executes the following SET ROLE command

  SET ROLE   Update_HR;

  UPDATE   Employee_v

   

  SET   Dept_Number=1001

  

  WHERE   Employee_Number=100001;

  Will this UPDATE statement succeed this time?

  Will this UPDATE statement succeed the next time Emp02 logs on? 

                                                        ----> to be Continued

SAP DB Frequently Asked Questions

1). What are the tools used in SAP Implementation? How do you create Alert Messages?  

Ans: One of the Tools being used for SAP Implementation is Quick Size, which is used to size the SAP Server based on number of users using various modules in SAP.

Alert Message is created in the CCMS monitor using Tcode RZ20 and RZ21. The values for Alert Auto Reaction Methods are defined using the Tcode RZ21, while this particular method is assigned to the 

Monitoring Tree Element (MTE) in the Tcode RZ20.

One as well can have an Alert Management System, where alerts from the Entire Landscape can be configured in a Central Monitoring System from where it can trigger a alert mail or SMS to the concerned Administrator

2). When spool buffer is full and new spool request is raised what happens to  

        the request? Where does the request stored? 

Ans: The new spool request will be in the queue. If it is urgent request then the current request will be stopped and new request will be send. 

3). What is the difference between R3trans and Tp in SAP-DBA? 

Ans: R/3trans is the R/3 system transport program which can be used for transporting data between different SAP systems, even when they don’t belong to the same group. R3trans normally is not used directory but called from the Tp control program or by the R/3 upgrade. Tp is the basic tool for the transporting the request. 

4). What is “Group by” in Select statement? 

Ans1:   Group by is used to fetch a unique data from a column. 

Ans2:
 

A "group by" is used to group selected rows in a table to perform arg functions on them. Rows not included in the "group by" clause and not part of ARG functions cannot be used ... (logical grouping of data) or operations performed (on groups of data) can be used with the "HAVING" clause to further refine the output.

Ex of some ARG functions: SUM (), MIN (), MAX (), AVG ()

5). Select statement to read data into internal tables. Types of Select statements 

Ans: select * from database table into (internal table name)
the above statement collects data into the header area of the internal table.
And
select * from database table into table (internal table name) 

the above statement collects the data into the body area directly.

6). Explain “Commit” and “Roll back”

Ans:   The Oracle RDBMS provides a transaction model based on a unit of work. The PL/SQL language supports most, but not all, of the database model for transactions (you cannot, for example, ROLLBACK FORCE). Transactions begin with the first change to data and end with either a COMMIT or ROLLBACK.

COMMIT makes the database changes permanent and visible to other database sessions.

ROLLBACK undoes the changes made in the current transaction either to the beginning of the transaction or to a save point. A save point is a named processing point in a transaction, created with the SAVEPOINT statement. Rolling back to a save point is a partial rollback of a transaction, wiping out all changes (and save points) that occurred later than the named save point.

7). What happens “Update” command is used without where clause?

Ans1: It will update all the records with same name in the particular field of the table. 

Ans2:

If we don’t mention "WHERE" clause in the UPDATE statement, it will update all records satisfying the given condition (if given any ) in the table ! 

Ans3:

Based on query condition. it will update all the fields which are mentioned in the query.
Ex: update employee set first name= @first name, last name=@last name where employee_id =@employee_id.
 

So in the above case nothing will be happen if you used where clause or not.
If you have many statements in the query you need to use the where clause.

8). What are logical databases? 

Ans1:

insert' will add a new record or a new row into the database table.
'Update' will modify a record in the DB table.
'Modify' it is a combination of both insert and update...

Ans2:
 

INSERT - Add a new record into the database table.
MODIFY - If record is available it modifies otherwise it wont modify.
UPDATE - If record is available its update the record otherwise it creates a new record.

9). Difference between “Insert”, “Update” and “Modify”

Ans:            INSERT - Add a new record into the database table.
                    MODIFY - If record is available it modifies otherwise it wont modify.
                    UPDATE - If record is available its update the record otherwise it creates a new record. 

10). “Catch” Command 

Ans: Trying to catch any runtime errors programmatically or manually we use this statement catch.

Tuning Tips for Application Engine - PeopleSoft

Application Engine programming has become the order of the day for most of the Custom development. Due to its very nature in its design, Application Engine has successfully replaced COBOL & to some extent SQR as main coding medium. Here are some of the tips that can be considered for improving the performance of Application Engine Programs. 

Temporary tables:

The Temporary tables can be used to create sets of data for processing especially in parallel processing. The process of improving the performance using Temp tables in App Engine Programs is

•  Truncate temp table in the beginning of the program as part of initialization routine

•  Populate temp table

•  Analyze temp table to generate statistics for optimizers (Use %UpdateStats meta-SQL)

•  Process as required

•  Some performance related points about Temporary tables are

PROCESS_INSTANCE

• If this field is keyed into temp table and application SQL includes it in the WHERE clause, then the table can be shared by multiple processes, this will hurt performance. Dedicated temporary table instance is the best way to go.

Defining Temporary tables

•  If the program accesses a set of rows repeatedly from a larger table, insert the necessary row into a temporary table in the beginning of the program. More on this in Set Processing section.

Statistics at Runtime for Temp tables

Temp tables experience heavy data volume changes during the course of a program run. To effectively gather statistics, for tuning them, %UpdateStats Meta-SQL can be used. Once the data is inserted into the temp table and committed then use this command, which will be interpreted by Oracle as ‘ANALYZE TABLE … ESTIMATE STATISTICS’ command. 

PeopleSoft stores the default syntax for ANALYZE command in a table PSDDLMODEL. Modification to that can be done through the script DDLORA.DMS, like adding a required SAMPLE ROWS/PERCENT for ESTIMATE clause

• Once batch-process runs are stabilized then turn off the %UpdateStats by the following two ways

           --> At Program level: Inactivate the steps in App Engine

                     --> Installation level: in Process Scheduler Configuration set DbFlags = 1 to turn of collecting statistics  

• Oracle PLAN_TABLE is wrapped automatically around all SQL statements in AE programs creating

Setting up number of Temporary table instances

• Switch on the App Engine SQL & Timings trace, if the command “UPDATE PS_AEONLINEINST… “is taking more time, then the number of temp table instances needs to be checked. 

•  Temp table instances [online] should be very small number (in PeopleTools Options page).

• Runtime box in Program Properties dialog box displays two options, if ‘Continue’ option is used, then base table will be used when all the temp table instances are assigned. If ‘Abort’ is selected then the program will abort when there are no temp tables to be assigned.

• Create temp tables in separate Tablespace and spread data files on multiple disks for minimizing I/O. Hardware disk striping is another way, check with DBA

• Create temp tables as Locally Managed Tablespace with fixed-extent size (See chapter on DISK I/O)

• When there is no commit before truncate step, AE converts Truncate command into DELETE statement. 

•  Identify such tables and manually truncate them at regular intervals to release buffer block & maximize performance.

• Build table instances:

          --> Do not execute the DDL script after building it in App Designer, Modify the script so that you can  

               place different sets of temp table on different tablespaces as per the instance number.

Global Temporary Tables in Oracle (v 8.x & higher)

Oracle creates Global Temporary tables which can be used for batch processing, they are session-specific and created at runtime in user’s Temporary Tablespace and are dropped once session is closed. At the time of creation, an option is given whether to preserve or delete the rows after commit. There are pros and cons for use of this feature

Pros:

· Reduction in redo

· Faster full scans – High Water Mark is set to zero (similar to %Truncate command)

· Faster Truncates due to space management in temporary segment

· Easier table management, since entire temp tables instances need not be created upfront. 

Cons:

· Table Statistics can not be gotten on these tables, will impact access paths and execution times

· Temporary table sizing should be done properly to avoid any runtime errors due to lack of extents

· ReStart option can not be used since data is lost when the session ends (similar to choosing  

      DERIVED/WORK record as State Record)

Indirect approach to using this feature is

• In Temp Tables tab in App Engine Properties window, set Instance Count to 0 and select ‘Continue’ radio button for RunTime Settings

• Build the Temp table and modify the script to create the tables as Global Temporary Tables

• When multiple runs of the program occurs, due to ‘Continue’ option selected, it’ll use the base temp table, which in turn uses Global Temporary Tables

Parallel Processing

Parallel Processing can be defined as having multiple instances of the same program running at the same time - each instance processing a discreet subset of the data. It is to be used when multiple rows of data need to be updated or processed; it is highly efficient in environments where there are multiple CPUs and partitioned data. Partition the data among multiple concurrent runs of a program each with its own dedicated version of temporary table using run control parameters. Steps involved in parallel processing are

• Define & Save temporary table records in App Designer (need not run SQL build process at this point)

• Assign temporary tables to App Engine programs and set instance counts dedicated for each program. Use %Table meta-SQL construct, to resolve table references to assigned temporary table instance dynamically at runtime. Run control parameters passed to each instance of temp table enable it to identify which input rows belong to it and each program instance inserts the rows from source into temp table instance using %Table

• Set number of total & online temporary table instances in PeopleTools Options page

• Build Temporary table in App Designer

ReUse Statement

• Used to Optimize SQL in batch programs, it converts any %BIND references to state records fields into real bind variables enabling the program to compile once at runtime, dedicate a cursor and re-execute with new data as often as program requires.

• ReUse is valid for SQL Actions

• Use ReUse only if you don’t use Bind variables for column names (since it converts %BIND fields to Bind variables)

• Use ReUse only if you have no %BINDs in the Select list.

• If SQL is dynamic, as in you are using %BIND to resolve to a value other than a standard Bind value and contents of Bind change each time the statement gets executed, then SQL statements don’t match with old queries in the cache, hence ReUse can’t be used. 

• If NOQUOTES modifier is used inside %BIND, its implied STATIC. For dynamic SQL substitution, %BIND has CHAR field and NOQUOTES to insert SQL rather than a literal value. If ReUse is enabled, then value of CHAR field gets substituted inline, instead of Bind marker. System executes SQL which is same as before even if the value of a static bind has changed. 

• %ClearCursor meta-SQL is used to recompile the statement.

Bulk Insert option is most effective in INSERT statements when ReUse statement is used. This will enable to store a large amount of data in the buffer before inserting in one shot. A flush also occurs before each commit. System ignores Bulk Insert setting in the following conditions:

·         SQL is not an INSERT

·         INSERT/SELECT, UPDATE, DELETE ignored

·         SQL does not have VALUES clause

·         SQL does not have a field list before VALUES clause.

·         All the three conditions are true: Oracle is the database, EFFDT is a field, and Record contains a mobile trigger. Because Oracle doesn’t allow reading of mutating tables in a row trigger.

Usage of One Row Records

• Suppose you have a single Insert statement into a table, followed by an %UpdateStats. If the stats were current before the Insert statement, and the Insert statement affects no rows, then the %UpdateStats is unnecessary

Planning to program for maximum performance

• Avoid DO loops as much as possible

•  Minimize your PeopleCode as much as possible to reduce the time in PeopleTools cache

• Avoid %BIND() without the ReUse option, since AE will pass literal values to database in its place

• Tune SQL and use parallel and set processing wherever possible

• Use Truncating command instead of delete command where there is no filter condition

ALTER SESSION Command

• To ensure Oracle Optimizer choose the right execution path every time and get optimal performance, try altering sessions in various parts of the program

• ‘SQLExec(“ALTER SESSION SET cursor_sharing = FORCE”);’

·  This will ensure that Oracle first checks the shared pool to see if there is an identical statement in shared pool

·  The two options for ‘cursor_sharing’ parameter are ‘FORCE’ & ‘SIMILAR’. The difference between the  two is that ‘SIMILAR’ forces similar statements to share SQL area without deteriorating the execution plans

Trace

• Setting Trace on AE programs can tell a lot about how the programs are being executed. In production environments, trace will add additional overhead to performance, ensure that trace value is set to zero once trace file is generated. Typical trace value is “TRACEAE = 2179”