EDIT MASK -Show - Display SQR Commands

SQR Commands – Show and Display and EDIT MASK

·   Display command useful to display only one value at a time

Display &STUDID Display &NAME Display &SEM Output: 100 Satish 4

Display &STUDID NO LINE Display &NAME NO LINE Display &SEM Output: 100 Satish 4

·   Show command useful to show more than one value at a time.

                                          

Show &STUDID &NAME &SEM Output: 100 Satish 4

·   We can also use the EDIT Mask with Show and Display.

Display '123456789' xxx-xx-xxxx Output: 123-45-6789

Display 1234567.89 999,999,999.99 Output: 1,234,567.89

Pseudo Code -File Operations - Reading from one file and writing into another file .

Reading from one file and writing into another file Pseudo Code.

Local array of string &FNAMES;

Local string &FILEPATH,&Lin;

Local File &File1,&File2;

&FILENAME2 = "c:\abc.txt";

&FILENAME1 = "d:\xyz.txt";/***Give the right path with file name***/

/***Opening File2 in Append mode and writing data into File2***/

&File2 = GetFile(&FILENAME2, "A", %FilePath_Absolute);

If &File2.IsOpen Then

   &TextPRINT = "Program  ABCEF";

   &File2.WriteLine(&TextPRINT);

End-If;

&File2.Close();

/***Opening File2 in Read mode ***/

&File2 = GetFile(&FILENAME2, "R", %FilePath_Absolute);

/***Check whether File1 Exists***/

If FileExists(&FILENAME1, %FilePath_Absolute) Then        

   &File1 = GetFile(&FILENAME1, "A", %FilePath_Absolute);

   /***read the data from File2 and  writing into File1***/

   If &File1.IsOpen Then

      While &File2.ReadLine(&Lin) 

         &File1.WriteLine(&Lin);

      End-While;

   End-If;

   &File1.Close();

   &File2.Close();

End-If;

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

Expected output

File1 d:\xyz will have the Data of File2 (c:\abc.txt)

On Break in SQR with Example

On Break in SQR

A break is a change in the value of a field.

Uses

1. Avoids printing redundant data.

2. Skip lines when the value of the Column is changed.

3. Print a value only if changed.

4. To add spaces in the report.

5. Perform conditional processing on variables that change.

6. Execute a special procedure before or after the break.

7. Print Subtotals.

Sample Programs – Break Logic?

Assume a table ABC is having following data.

1.      SKIPLINES

BEGIN-SELECT

COMPANY (,1) ON-BREAK SKIPLINES = 1

COUNTRY (,+5)

PAY_END_DT (,+5)

EMPLID (,+5)

NAME (,+5)

SALARY (,+5)

 POSITION (+1)

 FROM ABC

END-SELECT

Company will be printed only once and is not repeated.

Skiplines argument provides a line break between each break.

2.      SKIPLINES & LEVEL

BEGIN-SELECT

COMPANY (,1) ON-BREAK SKIPLINES = 2

COUNTRY (,+5) ON-BREAK LEVEL = 2 SKIPLINES = 1

PAY_END_DT (,+5)

EMPLID (,+5)

NAME (,+5)

SALARY (,+5)

 POSITION (+1)

 FROM ABC

END-SELECT

 

Best Practice to take back up Before deleting any data in Table

Before deleting any data in table it always Best Practice to take back up

So here is the Sample SQL 

Creating a Temp Table

CREATE TABLE SYSADM.PS_PO_HDR_TMP AS SELECT * FROM SYSADM.PS_PO_HDR WHERE BUSINESS_UNIT = 'MN300';

Make sure Table is created with Expected rows of data

SELECT * FROM SYSADM.PS_PO_HDR_TMP;

Delete rows from main table to remove data of backup data

DELETE FROM SYSADM.PS_PO_HDR

WHERE (BUSINESS_UNIT,PO_ID) IN  (SELECT BUSINESS_UNIT,PO_ID FROM SYSADM.PS_PO_HDR_TMP);

SQR App Engine Tracing Parameters with snaps and navigation

     SQR Tracing Parameters

Go to Process Scheduler – Process – Select any Process – Go to Override Tab – Select Parameter List type as APPEND 

Ways to Trace Application Engine?

a) Command line

By using –TRACE.

For Example:

PSAE.EXE —CT <DB_TYPE> -CD <DB_NAME> -CO <OPRID> -CP <PASSWORD> -R <RUN_CONTROL> -AI <PROGRAM_NAME> -I <PROCESS_INSTANCE> -TRACE <Value>

C:\PT8.45\bin\server\WINX86\psae.EXE -CT MICROSFT -CS PSNT -CD HC -CO PS -CP PS -R 123 -AI AETSTPROG -I 111 -TRACE 384

 b)Configuration Manager.

Set the Tracing option in Configuration Manager > Trace tab > A.E Trace.

c) Process Definition.

 Go to Process Scheduler – Process – Select any Process – Go to Override Tab – Select Parameter List type as APPEND and Specify the Trace option like -TRACE 7 -TOOLSTRACEPC 3980 –TOOLSTRACESQL 135.

d)Server configuration files.

We can enable the trace in the configuration files of Application Server and the Process Scheduler Server

Row-by-row processing VS Set Processing with Example

 Row By Row Processing

· A Single row will be fetched and rest of actions will be performed and then next row will be fetched and rest of actions will be performed and next and so on..

· Program will fetch the data Row by Row from database and performs the Subsequent Actions.

    Sample Program

· Assume a table ABC is having EMPLID, DEPTID, and SALARY.

· If we wanted to give 10% hike to the employees who are belongs to ‘BBB’ Department.

EMPLID	DEPTID	SALARY
11111	AAA	10,000
22222	BBB	10,000
33333	BBB	25,000
44444	CCC	12,000
55555	BBB	20,000

· With Row By Row Processing, AE Program Structure would be

MAIN    Step1       DoSelect %Select(EMPLID) Select EMPLID From ABC WHERE DEPITD = ’BBB’       SQL UPDATE ABC SET SALARY = (0.10*SALARY) + SALARY WHERE EMPLID = %Bind(EMPLID);

· DoSelect will fetch 22222, 33333, and 55555 employees.

· In First Loop - Inserts 22222 into State Record.

· Executes the SQL Action which updates the Salary with 10% hike for 22222 Employee.

· In Second Loop - Inserts 33333 into State Record and then updates the Salary.

· In Third Loop – Inserts 55555 into State Record and then Updates the Salary.

· Then comes out from the DoSelect.

· As the program in Row by Row Processing, it goes to the database 3 times and updates the Salary.

     Set Processing

Set processing is nothing but processing set of rows at one time rather than processing each row individually.

     Sample Program 

· With Set Processing, AE Program Structure would be

MAIN    Step1       SQL UPDATE ABC SET SALARY = (0.10*SALARY) + SALARY WHERE DEPTID = ‘BBB’;

· SQL Action will be executed which updates the Salary for all the 3 employees at a time.

Dynamic Call Section with Sample Program

 Procedure to Call Sections Dynamically

·    Create and assign a State Record with AE_APPLID and AE_SECTION fields.

·    Include DoSelect and insert AE_APPLID and AE_SECTIONID

(OR)

·    Include PeopleCode Action and based on the Conditions assign Program Name and Section Name.

Then

·    Make the Section as Dynamic by selecting Dynamic Check Box.

.      Sample Program 

MAIN   Step1     DoSelect %Select(AE_APPLID, AE_SECTIONID) Select AE_APPLID, AE_SECTIONID FROM PS_ABC OR     PeopleCode MessageBox(0, "", 0, 0, "Main Section - Step1 - PeopleCode"); If %OperatorId = "PS" Then    ABC_AET.AE_APPLID.Value = "ABC_APPENG";    ABC_AET.AE_SECTION.Value = "Insert"; Else    ABC_AET.AE_APPLID.Value = "ABC_APPENG";    ABC_AET.AE_SECTION.Value = "Delete"; End-If;     Call Section       Select Dynamic Check Box Delete Section   Step1     PeopleCode MessageBox(0, "", 0, 0, "Delete Section - Step1 - PeopleCode"); Insert Section   Step1     PeopleCode MessageBox(0, "", 0, 0, "Insert Section - Step1 - PeopleCode");

Application Engine Restart options with Snaps

Restart Facility

Restart Facility?

When there is an Abnormal Termination or Failure of an AE, we can restart the request from the same point where it got errored out i.e. the last successful Check Point.

Ways to Disable/Enable Restart facility?

1) Program Properties:

· In AE Program Properties – Advanced Tab – Disable Restart Check box.

1) Configuration Manager:

· In Configuration Manager – Profile Tab – Edit button – Process Scheduler Tab – Disable Restart check box.

 3) Command Line:

· Include the −DR Y option in the Command Line of PSAE.EXE.

How many ways we can Restart the AE Program?

·   From the Command Line.

This Option normally useful to the developers not for the end users.

C:\PT8.45\bin\server\WINX86\psae.EXE -CT <DBType> -CS <ServerName> -CD <DBName> -CO <OPRID> -CP <Password> -R <RncntlID> -AI <AE Name> -I <Process Instance>

·   From a Process Request page.

Open Process Scheduler by selecting PeopleTools, Process Scheduler, and System Process Requests.

Locate the Run Control ID of the program you need to restart.

To display the details of the failed process, click the Process Detail link.

On the Process Request Details page, select Restart Request, and click OK.

Required steps to implement restart facility?

·   One State Record should be SQL table.

·   Disable Restart should not be checked in Program Properties and Configuration Manager.