ORA-12899: error in PeopleSoft job

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - System Agent (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• Workload Automation Agent
• Autosys Workload Automation

Issue/Introduction

ORA-12899: Error in PeopleSoft job is seen, how to resolve this?

 STARTING        08/06/2020 12:43:27    1  PD  08/06/2020 12:43:26   server.company
  RUNNING         08/06/2020 12:43:27    1  PD  08/06/2020 12:43:27   server.company
  FAILURE         08/06/2020 12:43:27    1  PD  08/06/2020 12:43:27
    <Failed to perform SQL: insert into PSPRCSRQST(PRCSINSTANCE, JOBINSTANCE, MAINJOBINSTANCE, PRCSJOBSEQ, PRCSJOBNAME, PRCSTYPE, PRCSNAME, PRCSITEMLEVEL, MAINJOBNAME, MAINJOBSEQ, RUNLOCATION, OPSYS, DBTYPE, DBNAME, SERVERNAMERQST, SERVERNAMERUN, RUNDTTM, RECU>
  [*** ALARM ***]
    JOBFAILURE    08/06/2020 12:43:28    1  PD  08/06/2020 12:43:28   server.company
 
Agent shows:

 08/07/2020 05:30:00.826-0400 1 PSPlugin.ps Internal Thread.CybWOBRunPsHandler.processWob[:148] - Table update problem. for AFM: 20200807 05300099+0400 DEV_AGENT JavaAgent#tcpip@DEV_SCH 437281.208157976_1/WAAE_WF0.1/MAIN RUN PS SkipParmUpdates(No) ServerName(PSUNX) RunCntlId(FN_APPMSG_ERR_NOTIFY) Dr(Yes) ProcessType(Application Engine) ProcessName(FN_TEPT009) PsOprId(PRODBATCH) PsOpr() EmailWebReport(No) EmailLog(No) MFUser(...) WOBRequestID(EF80164EAD6F8A3BD5F68CAA7C0E5E9CA4CE18E115967926007460)
 08/07/2020 05:30:00.826-0400 1 PSPlugin.ps Internal Thread.CybWOBRunPsHandler.processWob[:149] - cybermation.plugins.ps.PsPrcsRqstUpdateExeption: Failed to perform SQL: insert into PSPRCSRQST(PRCSINSTANCE, JOBINSTANCE, MAINJOBINSTANCE, PRCSJOBSEQ, PRCSJOBNAME, PRCSTYPE, PRCSNAME, PRCSITEMLEVEL, MAINJOBNAME, MAINJOBSEQ, RUNLOCATION, OPSYS, DBTYPE, DBNAME, SERVERNAMERQST, SERVERNAMERUN, RUNDTTM, RECURNAME, OPRID, PRCSVERSION, RUNSTATUS, RQSTDTTM, LASTUPDDTTM, BEGINDTTM, ENDDTTM, RUNCNTLID, PRCSRTNCD, CONTINUEJOB, USERNOTIFIED, INITIATEDNEXT, OUTDESTTYPE, OUTDESTFORMAT, ORIGPRCSINSTANCE, GENPRCSTYPE, RESTARTENABLED, TIMEZONE, PSRF_FOLDER_NAME, SCHEDULENAME, RETRYCOUNT, RECURORIGPRCSINST, P_PRCSINSTANCE, DISTSTATUS, PRCSCATEGORY, PRCSCURREXPIREDTTM, RUNSERVEROPTION, PT_RETENTIONDAYS, CONTENTID, PTNONUNPRCSID) values(3804406, 0, 0, 0, ' ', 'Application Engine', 'FN_TEPT009', 0, ' ', 0, '2', '4', '2', 'customer_oracle_db_name', 'PSUNX', ' ', SYSDATE, ' ', 'PRODBATCH', 0, '4', SYSDATE, SYSDATE, NULL, NULL, 'FN_APPMSG_ERR_NOTIFY', 0, 0, 0, 0, '6', '14', 3804406, '5', '0', ' ', ' ', ' ', 0, 3804406, 0, '1', 'Default', NULL, '1', 45, 4616184, ' ')
                           at cybermation.plugins.ps.PsDbHandler.insertDbEntry(PsDbHandler.java:818)
                           at cybermation.plugins.ps.PsRequestData8_4.insertPsRequest(PsRequestData8_4.java:325)
                           at cybermation.plugins.ps.CybWOBRunPsHandler.processWob(CybWOBRunPsHandler.java:95)
                           at cybermation.plugins.library.base.CybRMIExternalPluginBaseManager.processWob(CybRMIExternalPluginBaseManager.java:627)
                           at cybermation.plugins.library.base.CybRMIExternalPluginBaseManager.run(CybRMIExternalPluginBaseManager.java:460)
                           at java.lang.Thread.run(Thread.java:821)
                          Caused by: java.sql.SQLException: ORA-12899: value too large for column "SYSADM"."PSPRCSRQST"."DBNAME" (actual: 10, maximum: 8)
                           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
                           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
                           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
                           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
                           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
                           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
                           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
                           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
                           at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
                           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
                           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
                           at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
                           at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3756)
                           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3736)
                           at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1063)
                           at cybermation.plugins.ps.PsDbHandler.runUpdateNoReconnect(PsDbHandler.java:592)
                           at cybermation.plugins.ps.PsDbHandler.runUpdate(PsDbHandler.java:566)
                           at cybermation.plugins.ps.PsDbHandler.insertDbEntry(PsDbHandler.java:813)
                           at cybermation.plugins.ps.PsRequestData8_4.insertPsRequest(PsRequestData8_4.java:325)
                           at cybermation.plugins.ps.CybWOBRunPsHandler.processWob(CybWOBRunPsHandler.java:95)
                           at cybermation.plugins.library.base.CybRMIExternalPluginBaseManager.processWob(CybRMIExternalPluginBaseManager.java:627)
                           at cybermation.plugins.library.base.CybRMIExternalPluginBaseManager.run(CybRMIExternalPluginBaseManager.java:460)
                           at java.lang.Thread.run(Thread.java:821)
                          Caused by: oracle.jdbc.OracleDatabaseException: ORA-12899: value too large for column "SYSADM"."PSPRCSRQST"."DBNAME" (actual: 10, maximum: 8)
                           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
                           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
                           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
                           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
                           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
                           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
                           

 

 

 

 

Environment

Release : 12.0

Component : CA Workload Automation Agent for PeopleSoft

Cause

Peoplesoft.log file shows an error like this, which is with your database name customer_oracle_db_name  with 10 characters in the name while the Oracle database table PSPRCSRQST  has 8 character limitation. 

 insert into PSPRCSRQST(PRCSINSTANCE, JOBINSTANCE, MAINJOBINSTANCE, PRCSJOBSEQ, PRCSJOBNAME, PRCSTYPE, PRCSNAME, PRCSITEMLEVEL, MAINJOBNAME, MAINJOBSEQ, RUNLOCATION, OPSYS, DBTYPE, DBNAME, SERVERNAMERQST, SERVERNAMERUN, RUNDTTM, RECURNAME, OPRID, PRCSVERSION, RUNSTATUS, RQSTDTTM, LASTUPDDTTM, BEGINDTTM, ENDDTTM, RUNCNTLID, PRCSRTNCD, CONTINUEJOB, USERNOTIFIED, INITIATEDNEXT, OUTDESTTYPE, OUTDESTFORMAT, ORIGPRCSINSTANCE, GENPRCSTYPE, RESTARTENABLED, TIMEZONE, PSRF_FOLDER_NAME, SCHEDULENAME, RETRYCOUNT, RECURORIGPRCSINST, P_PRCSINSTANCE, DISTSTATUS, PRCSCATEGORY, PRCSCURREXPIREDTTM, RUNSERVEROPTION, PT_RETENTIONDAYS, CONTENTID, PTNONUNPRCSID) 
    values(3804406, 0, 0, 0, ' ', 'Application Engine', 'FN_TEPT009', 0, ' ', 0, '2', '4', '2', 'customer_oracle_db_name', 'PSUNX', ' ', SYSDATE, ' ', 'PRODBATCH', 0, '4', SYSDATE, SYSDATE, NULL, NULL, 'FN_APPMSG_ERR_NOTIFY', 0, 0, 0, 0, '6', '14', 3804406, '5', '0', ' ', ' ', ' ', 0, 3804406, 0, '1', 'Default', NULL, '1', 45, 4616184, ' ')

In the database, the field DBNAME is VARCHAR2 (8) Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.7.0.0.0

Customer's TNSNames.ora 

customer_orcl_name =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = customer_oracle_host)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = customer_oracle_db_name)
   )
  )
  

Customer's DBA clarified that as they are using RAC, and that in their case using SID is not applicable. They have to use SERVICE_NAME.  SID is specific to the instance its running on and we are RAC aware and run on 2 nodes, so SID gets the suffix of the node its running on.  Example: realSID_name is the DB name and runs on nodes 4 and 5, so the SID is realSID4 and realSID5 and that the connection has to be made with 3 elements – host, port, and service (which works in existing envs). This would enable them to use the RAC awareness and workload balance.

 

The error occurs if agentparm.txt  has a parameter called ps.db.name  with the Oracle database name as the value, example

current (bad) agentparm.txt has
ps.db.name=customer_oracle_db_name
 

Resolution

If Agentparm.txt has the entry ps.db.name has a value, then we would the plugin would use PSPRCSRQST table (which was passing customer_oracle_db_name as the dbname value into a 8 character column and so the Oracle error).  

If this ps.db.name parameter is not present,  we would use psdbowner table (and use the dbname specified in the JDBC URL).   psdbowner is an out of the box table from PeopleSoft. 

 

To resolve the issue, comment the line ps.db.name=customer_oracle_db_name in agentparm.txt, restart agent and retest