Troubleshooting EIPs
This topic discusses how to:
Use structured query language (SQL).
Use and test PeopleSoft Application Engine programs.
Note: The stream IDs must be changed to reflect the configuration in the system. The stream ID sections that need to be changed are in italics.
Following are a series of sample SQL statements.
Validation SQL Statements
This sample code shows validation statements:
select
PS_QS_STREAM8.STREAM_ID,
PS_QS_STREAM8.QS_APP_CONTEXT
FROM PS_QS_STREAM8,PS_QS_STREAM_ROOT,PS_QS_APP_DEF
WHERE PS_QS_STREAM8.STREAM_ID >= 164 AND PS_QS_STREAM8.STREAM_ID <= 164
and PS_QS_STREAM8.STREAM_ROOT_ID = PS_QS_STREAM_ROOT.STREAM_ROOT_ID
and PS_QS_APP_DEF.QS_APP_CONTEXT = PS_QS_STREAM_ROOT.QS_APP_CONTEXT
ORDER BY STREAM_ID
select
PS_QS_STREAM8.STREAM_ID
FROM PS_QS_STREAM8,PS_QS_STREAM_ROOT
WHERE PS_QS_STREAM8.STREAM_ID >= 164 AND PS_QS_STREAM8.STREAM_ID <= 164
and PS_QS_STREAM8.STREAM_ROOT_ID = PS_QS_STREAM_ROOT.STREAM_ROOT_ID
ORDER BY STREAM_ID
select
STREAM_ID,
CLIM_STS_CD,
CLIM_ID,
STR_SGRP_CNT,
STR_SGRP_SEQ,
CUR_SGRP_SIZE,
CUR_SPEC_VIO_CNT,
CUR_PLOT_VALUE1,
CUR_PLOT_VALUE2,
RUN_MOMENT,
TSWIN_BUFFER1,
TSWIN_BUFFER2
FROM PS_QS_STREAM8
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
select
BUSINESS_UNIT,
STREAM_ID,
CLIM_ID,
CLIM_STS_CD,
CLIM_UPPER_X,
CLIM_UX_IND,
CLIM_MEAN_X,
CLIM_MX_IND,
CLIM_LOWER_X,
CLIM_LX_IND,
CLIM_UPPER_MRANGE,
CLIM_UMRANGE_IND,
CLIM_MEAN_RANGE,
CLIM_MRANGE_IND,
CLIM_LOWER_MRANGE,
CLIM_LMRANGE_IND,
CLIM_CRE_DTTM,
CLIM_CRE_BY
from PS_QS_CNTL_LIM_HIS
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
ORDER BY CLIM_ID
select
BUSINESS_UNIT,
SESSN_ID,
STREAM_ID,
SESSN_STREAM_SEQ,
SESSN_SGRP_SEQ,
SESSN_STRM_STS,
SESSN_SGRP_SIZE,
MFDS_DATA_TYPE_CD,
SESSN_ANLZ_READY,
CLIM_ID,
SPEC_ID,
SRSLT_P1,
SRSLT_P1_IND,
SRSLT_P2,
SRSLT_P2_IND,
CLIM_STS_CD,
SPEC_VIO_SW,
QS_ANALYZER_STATUS,
QS_ALARM_CNT,
QS_NVALUES,
QS_VALUEREADING_1,
QS_VALUEREADING_2,
QS_VALUEREADING_3,
QS_VALUEREADING_4,
QS_VALUEREADING_5,
DISPATCH_FLAG,
SESSN_SUBMIT_DTTM,
SRSLT_CRE_DTTM,
QS_ACTION_CNT,
QS_CAUSE_CNT,
QS_COMM_CNT,
OPERATOR,
QS_UAID
from PS_QS_SUBGROUP
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
ORDER BY SESSN_ID
select
BUSINESS_UNIT,
SESSN_ID,
STREAM_ID,
SAMPLE,
TEST_SEQ,
TEST_ID
from PS_QS_SESSN_TEST8
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
ORDER BY SESSN_ID
Generic SQL Statements
This sample code shows generic statements:
select
STREAM_ID,
CLIM_STS_CD,
CLIM_ID,
STR_SGRP_CNT,
STR_SGRP_SEQ,
CUR_SGRP_SIZE,
CUR_SPEC_VIO_CNT,
CUR_PLOT_VALUE1,
CUR_PLOT_VALUE2,
RUN_MOMENT,
TSWIN_BUFFER1,
TSWIN_BUFFER2
FROM PS_QS_STREAM8
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
select * from PS_QS_CNTL_LIM_HIS WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
select PS_QS_SESSN_HDR8.* from PS_QS_SUBGROUP,PS_QS_SESSN_HDR8
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
AND PS_QS_SESSN_HDR8.SESSN_ID = PS_QS_SUBGROUP.SESSN_ID
select PS_QS_SESSN_TRACE8.* from PS_QS_SUBGROUP,PS_QS_SESSN_TRACE8
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164 AND PS_QS_SESSN_TRACE8.SESSN_ID = PS_QS_SUBGROUP.SESSN_ID
select * from PS_QS_SUBGROUP WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
select * from PS_QS_SESSN_DFCT8 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
select * from PS_QS_SESSN_TEST8 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
SQL to Reset Database Tables
This sample code shows statements to reset database tables:
update PS_QS_STREAM8 set
CLIM_STS_CD = "NONE",
CLIM_ID = "NONE",
STR_SGRP_CNT = 0,
STR_SGRP_SEQ = 0,
CUR_SGRP_SIZE = 0,
CUR_SPEC_VIO_CNT = 0,
CUR_PLOT_VALUE1 = 0,
CUR_PLOT_VALUE2 = 0,
RUN_MOMENT = 0,
TSWIN_BUFFER1 = "",
TSWIN_BUFFER2 = ""
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
delete from PS_QS_SESSN_HDR8 WHERE SESSN_ID in (select SESSN_ID from PS_QS_SUBGROUP
where STREAM_ID >= 164 AND STREAM_ID <= 164)
delete from PS_QS_SESSN_TRACE8 WHERE SESSN_ID in (select SESSN_ID from PS_QS_SUBGROUP
where STREAM_ID >= 164 AND STREAM_ID <= 164)
delete from PS_QS_SESSN_DFCT8 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
delete from PS_QS_CNTL_LIM_HIS WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
delete from PS_QS_SESSN_TEST8 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
delete from PS_QS_SUBGROUP WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
SQL to Retrieve Stream Information
This sample code shows statements to retrieve information:
select
STREAM_ID,
MFDS_ID,
CLIM_STS_CD,
STR_SGRP_CNT,
STR_SGRP_SEQ,
CLIM_ID,
CUR_SGRP_SIZE,
CUR_SPEC_VIO_CNT,
CUR_PLOT_VALUE1,
CUR_PLOT_VALUE2,
RUN_MOMENT,
TSWIN_BUFFER1,
TSWIN_BUFFER2,
SPEC_ID,
SPEC_LSL,
SPEC_LSL_IND,
SPEC_USL,
SPEC_USL_IND,
SPEC_FACTOR1,
SPEC_FACTOR1_IND,
SPEC_FACTOR2,
SPEC_FACTOR2_IND,
MFDS_COLLECT_SW,
MFDS_NAME,
MFDS_SGRP_SIZE,
MFDS_DATA_TYPE_CD,
MFDS_LAL,
MFDS_LAL_IND,
MFDS_UAL,
MFDS_UAL_IND,
MFDS_INC_CD,
MFDS_FORMULA,
CHART_ID,
PROC_ID,
CLIM_UPPER_X,
CLIM_UX_IND,
CLIM_MEAN_X,
CLIM_MX_IND,
CLIM_LOWER_X,
CLIM_LX_IND,
CLIM_UPPER_MRANGE,
CLIM_UMRANGE_IND,
CLIM_MEAN_RANGE,
CLIM_MRANGE_IND,
CLIM_LOWER_MRANGE,
CLIM_LMRANGE_IND
FROM PS_QS_API_STR83_VW
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
ORDER BY STREAM_ID
This is an example of a Application Engine program that reads an ASCII text file and posts a Quality EIP message. The following sample displays an input file for the Application Engine program:
/* Read source file and publish messages into EIP message queue. */
Local File &TestDataIn, &LogFileOut;
Local string &LineIn, &LogMsg, &FilenameIn, &LogFilename;
Local number &iRowSet, &WriteStatus, &nLinesIn, &nSessionGroups, &nSessions;
Local number &nSubgroups, &nDefects, &nTraceSets, &iValue;
Local number &iSession, &iSubgroup, &iDefect, &iTrace;
/* record keys */
Local number &SessionGroupId, &SessionId, &StreamId;
Local string &DataSource;
/* record fields */
Local string &Fieldname, &Fieldvalue;
Local Message &Message;
Local Rowset &SessionGroupRs, &SessionRs, &SubgroupRs, &DefectRs, &TraceRs;
Local Row &SessionGroupRow, &SessionRow, &SubgroupRow, &DefectRow, &TraceRow;
Local Record &SessionGroupRec, &SessionRec, &SubgroupRec, &DefectRec, &TraceRec;
Local datetime &SessionCreateDttm;
/* end of declarations *****************************************************/
&nLinesIn = 0;
&nSessionGroups = 0;
&nSessions = 0;
&nSubgroups = 0;
&nDefects = 0;
&nTraceSets = 0;
&FilenameIn = "EIPTestDataIn.txt";
&LogFilename = "EIPTestDataIn.log";
&LogFileOut = GetFile(&LogFilename, "W", "A", %FilePath_Absolute);
If (&LogFileOut.IsOpen = False) Then
Return;
End-If;
&LogFileOut.WriteLine("Processing started " | %Datetime | ", [" | &FilenameIn | "]");
&LogFileOut.WriteLine("");
&TestDataIn = GetFile(&FilenameIn, "E", "A", %FilePath_Absolute);
If (&TestDataIn.IsOpen = False) Then
&LogMsg = "Unable to open input file [" | &FilenameIn | "]";
&LogFileOut.WriteLine(&LogMsg);
&LogFileOut.Close();
Return;
End-If;
&Message = CreateMessage(Message.QUALITY_DATA_SUBMIT);
If (&Message = Null) Then
&LogMsg = "Unable to create message";
&LogFileOut.WriteLine(&LogMsg);
&LogFileOut.Close();
Return;
End-If;
If (&Message.IsActive = False) Then
&LogMsg = "Message is inactive";
&LogFileOut.WriteLine(&LogMsg);
&LogFileOut.Close();
Return;
End-If;
&SessionGroupRs = &Message.GetRowset();
While &TestDataIn.ReadLine(&LineIn)
&nLinesIn = &nLinesIn + 1;
&LogMsg = "Processing line " | &nLinesIn | ", [" | &LineIn | "]";
&LogFileOut.WriteLine(&LogMsg);
/* We need to do publish messages here... */
/* identify the record type */
If (Substring(&LineIn, 1, 13) = "session group") Then
/* check to make sure that we don't overrun our output message buffer */
If ((&Message.Size + 100000) > %MaxMessageSize) Then
&Message.Publish();
&Message = CreateMessage(Message.QUALITY_DATA_SUBMIT);
If (&Message = Null) Then
&LogFileOut.WriteLine("Unable to create message");
&LogFileOut.Close();
Return;
End-If;
&SessionGroupRs = &Message.GetRowset();
End-If;
&nSessionGroups = &nSessionGroups + 1;
&SessionGroupId = Value(LTrim(Substring(&LineIn, 15, 4)));
&DataSource = Substring(&LineIn, 22, 12);
If (&nSessionGroups > 1) Then
&SessionGroupRs.InsertRow(&nSessionGroups - 1);
End-If;
&SessionGroupRow = &SessionGroupRs.GetRow(&nSessionGroups);
&SessionGroupRec = &SessionGroupRow.GetRecord(Record.QS_EIP_SESS_GRP);
/* set keys */
&SessionGroupRec.QS_EIP_SESSION_GRP.Value = &SessionGroupId;
&SessionGroupRec.QS_EIP_DATASOURCE.Value = &DataSource;
&iSession = 0;
Else
If (Substring(&LineIn, 1, 9) = " session") Then
/* stop the last timer and start a new one */
&nSessions = &nSessions + 1;
&iSession = &iSession + 1;
&SessionRs = &SessionGroupRow.GetRowset(1);
/* original -> &SessionId = Value(LTrim(Substring(&LineIn, 14, 5))); */
/* using statement below to autonumber */
&SessionId = &iSession;
If (&iSession > 1) Then
&SessionRs.InsertRow(&iSession - 1);
End-If;
&SessionRow = &SessionRs.GetRow(&iSession);
&SessionRec = &SessionRow.GetRecord(Record.QS_EIP_SESSION);
/* set keys */
&SessionRec.QS_EIP_SESSION_GRP.Value = &SessionGroupId;
&SessionRec.QS_EIP_DATASOURCE.Value = &DataSource;
&SessionRec.QS_EIP_SESSN_ID.Value = &SessionId;
/* set fields */
&SessionRec.BUSINESS_UNIT.Value = LTrim(Substring(&LineIn, 22, 10));
&SessionCreateDttm = %Datetime;
&SessionRec.SESSN_CRE_DTTM.Value = &SessionCreateDttm;
/* Set session status to saved = 1, move = 2 */
&SessionRec.QS_SESSION_STATUS.Value = 1;
/* set traceset to locked = 2, not locked = 1 */
&SessionRec.QS_EIP_TRACELOCK.Value = 2;
&iSubgroup = 0;
&iTrace = 0;
Else
If (Substring(&LineIn, 1, 12) = " subgroup") Then
&nSubgroups = &nSubgroups + 1;
&iSubgroup = &iSubgroup + 1;
For &iRowSet = 1 To &SessionRow.ChildCount
If (&SessionRow.GetRowset(&iRowSet).DBRecordName =
"QS_EIP_SGRP_SUB") Then Break;
End-If;
End-For;
&SubgroupRs = &SessionRow.GetRowset(&iRowSet);
&StreamId = Value(LTrim(Substring(&LineIn, 14, 5)));
If (&iSubgroup > 1) Then
&SubgroupRs.InsertRow(&iSubgroup - 1);
End-If;
&SubgroupRow = &SubgroupRs.GetRow(&iSubgroup);
&SubgroupRec = &SubgroupRow.GetRecord(Record.QS_EIP_SGRP_SUB);
/* set keys */
&SubgroupRec.QS_EIP_SESSION_GRP.Value = &SessionGroupId;
&SubgroupRec.QS_EIP_DATASOURCE.Value = &DataSource;
&SubgroupRec.QS_EIP_SESSN_ID.Value = &SessionId;
&SubgroupRec.STREAM_ID.Value = &StreamId;
/* set fields */
/* set subgroup status to submitted = 2, saved = 1 */
&SubgroupRec.QS_SUBGROUP_STATUS.Value = 2;
&SubgroupRec.SESSN_CRE_DTTM.Value = &SessionCreateDttm;
&SubgroupRec.SESSN_SUBMIT_DTTM.Value = %Datetime;
&SubgroupRec.OPERATOR.Value = %OperatorId;
&SubgroupRec.SESSN_SGRP_SIZE.Value =
Value(LTrim(Substring(&LineIn, 20, 8)));
&SubgroupRec.QS_NVALUES.Value = Value(LTrim(Substring(&LineIn, 29, 8)));
For &iValue = 1 To &SubgroupRec.QS_NVALUES.Value
&Fieldname = "FIELD.QS_VALUEREADING_" | &iValue;
&SubgroupRec.GetField(@&Fieldname).Value =
Value(RTrim(LTrim(Substring(&LineIn, 38 + ((&iValue - 1) * 9), 8))));
End-For;
&iDefect = 0;
Else
If (Substring(&LineIn, 1, 12) = " defect") Then
&nDefects = &nDefects + 1;
&iDefect = &iDefect + 1;
For &iRowSet = 1 To &SubgroupRow.ChildCount
If (&SubgroupRow.GetRowset(&iRowSet).
DBRecordName = "QS_EIP_DEFECTS") Then
Break;
End-If;
End-For;
&DefectRs = &SubgroupRow.GetRowset(&iRowSet);
If (&iDefect > 1) Then
&DefectRs.InsertRow(&iDefect - 1);
End-If;
&DefectRow = &DefectRs.GetRow(&iDefect);
&DefectRec = &DefectRow.GetRecord(Record.QS_EIP_DEFECTS);
/* set keys */
&DefectRec.QS_EIP_SESSION_GRP.Value = &SessionGroupId;
&DefectRec.QS_EIP_DATASOURCE.Value = &DataSource;
&DefectRec.QS_EIP_SESSN_ID.Value = &SessionId;
&DefectRec.STREAM_ID.Value = &StreamId;
&DefectRec.SAMPLE.Value = Value(LTrim(Substring(&LineIn, 16, 5)));
&DefectRec.DFCT_SEQ.Value = Value(LTrim(Substring(&LineIn, 22, 3)));
/* set fields */
&DefectRec.DFCT_L1_DEFN.Value = RTrim(Substring(&LineIn, 28, 10));
&DefectRec.DFCT_L2_DEFN.Value = RTrim(Substring(&LineIn, 39, 10));
&DefectRec.DFCT_L3_DEFN.Value = RTrim(Substring(&LineIn, 50, 10));
&DefectRec.DFCT_CNT.Value = Value(LTrim(Substring(&LineIn, 61, 5)));
Else
If (Substring(&LineIn, 1, 12) = " traceset") Then
&nTraceSets = &nTraceSets + 1;
&iTrace = &iTrace + 1;
For &iRowSet = 1 To &SessionRow.ChildCount
If (&SessionRow.GetRowset(&iRowSet).DBRecordName = "QS_EIP_TRACE") Then
Break;
End-If;
End-For;
&TraceRs = &SessionRow.GetRowset(&iRowSet);
If (&iTrace > 1) Then
&TraceRs.InsertRow(&iTrace - 1);
End-If;
&TraceRow = &TraceRs.GetRow(&iTrace);
&TraceRec = &TraceRow.GetRecord(Record.QS_EIP_TRACE);
/* set keys */
&TraceRec.QS_EIP_SESSION_GRP.Value = &SessionGroupId;
&TraceRec.QS_EIP_DATASOURCE.Value = &DataSource;
&TraceRec.QS_EIP_SESSN_ID.Value = &SessionId;
&TraceRec.SAMPLE.Value = &iTrace;
/* set operator and date/time stamp trace field defaults */
&TraceRec.OPERATOR.Value = %OperatorId;
&TraceRec.STAMP_DTTM.Value = %Datetime;
Else
/* set trace fields */
If (Substring(&LineIn, 1, 11) = " field") Then
&Fieldname = "FIELD." | RTrim(Substring(&LineIn, 16, 14));
&Fieldvalue = RTrim(LTrim(Substring(&LineIn, 30, 20)));
&TraceRec.GetField(@&Fieldname).Value = &Fieldvalue;
Else
/* ignore comment lines */
If ( Not ((Substring(&LineIn, 1, 2) = "//") Or
(&LineIn = ""))) Then
&LogMsg = "Invalid line [" | &LineIn | "]";
&LogFileOut.WriteLine(&LogMsg);
End-If;
End-If;
End-If;
End-If;
End-If;
End-If;
End-If;
End-While;
&LogFileOut.WriteLine("");
&LogMsg = "Message size = " | &Message.Size | ",
maximum message size = " | %MaxMessageSize;
&LogFileOut.WriteLine(&LogMsg);
&Message.Publish();
/* cleanup */
&TestDataIn.Close();
&LogFileOut.WriteLine("");
&LogMsg = &nSessions | " sessions, " | &nSubgroups | " subgroups, " |
&nDefects | " defects, " | &nTraceSets | " tracesets.";
&LogFileOut.WriteLine(&LogMsg);
&LogMsg = "Processing completed " | %Datetime | ", " | &nLinesIn |
" lines processed, closing log file.";
&LogFileOut.WriteLine(&LogMsg);
&LogFileOut.Close();
Application Engine Sample Input Test File
This test file is only an example. For the file to be useful, you must change the information in the example to reflect the information in the configuration.
// Test set
//
session group 2 EIP-AE
session 1 M04
subgroup 140 3 3 13 11 12
subgroup 141 3 3 24 23 23
subgroup 142 3 3 34 33 33
session 2 M04
subgroup 140 3 3 12 12 13
subgroup 141 3 3 22 22 22
subgroup 142 3 3 32 32 32
session 3 M04
subgroup 140 3 3 11 12 13
subgroup 141 3 3 21 22 23
subgroup 142 3 3 31 32 33
session 4 M04
subgroup 140 3 3 13 11 13
subgroup 141 3 3 13 11 13
subgroup 142 3 3 13 11 13
session 5 M04
subgroup 140 3 3 12 11 13
subgroup 141 3 3 12 11 13
subgroup 142 3 3 12 11 13
session 6 M04
subgroup 140 3 3 14 11 13
subgroup 141 3 3 14 11 13
subgroup 142 3 3 14 11 13
session 7 M04
subgroup 140 3 3 12 12 12
subgroup 141 3 3 22 22 22
subgroup 142 3 3 22 22 22
session 8 M04
subgroup 140 3 3 13 12 13
subgroup 141 3 3 13 12 13
subgroup 142 3 3 13 12 13
session 9 M04
subgroup 140 3 3 14 13 11
subgroup 141 3 3 14 13 11
subgroup 142 3 3 14 13 11
session 10 M04
subgroup 140 3 3 14 13 12
subgroup 141 3 3 13 11 11
subgroup 142 3 3 13 11 11
session 11 M04
subgroup 140 3 3 13 11 11
subgroup 141 3 3 12 11 12
subgroup 142 3 3 12 11 12
session 12 M04
subgroup 140 3 3 12 11 12
subgroup 141 3 3 13 11 13
subgroup 142 3 3 13 11 13
session 13 M04
subgroup 140 3 3 13 11 13
subgroup 141 3 3 12 12 13
subgroup 142 3 3 12 12 13
session 14 M04
subgroup 140 3 3 12 12 13
subgroup 141 3 3 13 12 11
subgroup 142 3 3 13 12 11
session 15 M04
subgroup 140 3 3 13 12 11
subgroup 141 3 3 11 13 13
subgroup 142 3 3 11 13 13
session 16 M04
subgroup 140 3 3 11 13 13
subgroup 141 3 3 22 22 22
subgroup 142 3 3 22 22 22
session 17 M04
subgroup 140 3 3 12 12 12
subgroup 141 3 3 13 12 11
subgroup 142 3 3 13 12 11
session 18 M04
subgroup 140 3 3 13 12 11
subgroup 141 3 3 14 11 12
subgroup 142 3 3 11 12 14
session 19 M04
subgroup 140 3 3 14 11 12
subgroup 141 3 3 11 12 14
subgroup 142 3 3 12 12 14
session 20 M04
subgroup 140 3 3 11 12 14
subgroup 141 3 3 12 12 14
subgroup 142 3 3 14 11 12
session 21 M04
subgroup 140 3 3 12 12 14
subgroup 141 3 3 13 11 11
subgroup 142 3 3 13 11 11
session 22 M04
subgroup 140 3 3 13 11 11
subgroup 141 3 3 12 11 12
subgroup 142 3 3 12 11 12
session 23 M04
subgroup 140 3 3 12 11 12
subgroup 141 3 3 12 11 11
subgroup 142 3 3 12 11 11
session 24 M04
subgroup 140 3 3 11 12 11
subgroup 141 3 3 11 14 13
subgroup 142 3 3 11 14 13
session 25 M04
subgroup 140 3 3 11 14 13
subgroup 141 3 3 13 11 12
subgroup 142 3 3 13 11 12