AutoSys Workload Automation

 View Only
  • 1.  Oracle 19c chokes when a wildcarded autorep with a leading % is executed

    Posted 5 days ago

    Hi AutoSys Experts, 

    We've had serious Oracle SQL execution delays when an autorep command with leading % (% in the front) is submitted.

    For Example: autorep -J %job%pattern% 

    As per our Oracle DBA Engineers, the '%' at the start causes it not to use the index so it does a full table/index scans and very expensive I/O (DBA's words: "it is not using index as bind3 "t0.job_name LIKE :3 " starts with %.")

    We are sick and tired of chasing our users asking them not to do a autorep -J %pattern% search, but there are still some breachers.

    Wish we had something like autoextvj for the autorep/autostatus command that we could use to deny/reject this offensive %pattern%. 

    Was looking at the EEM as-job Explicit Deny policy to deny anything with pattern POC.%.* (Regex, POC is $AUTOSERV), but it does not seem to work the way I initially thought it would. It appears autorep still manages to sumit the query with the percent bypassing EEM (Maybe EEM treats it as a literal, not a wildcard). 

    Has anyone come across this behaviour? If yes, how did you tackle it? 

    Many thanks!

    Chandru V



  • 2.  RE: Oracle 19c chokes when a wildcarded autorep with a leading % is executed

    Posted 4 days ago

    Hi Chandru,

    Did you try an as-job explicit deny with a Resource something like  ^POC.[^%] and 'Treat as regex' ticked? 




  • 3.  RE: Oracle 19c chokes when a wildcarded autorep with a leading % is executed

    Posted 3 days ago

    Hi Antony, 

    Tried that as well, no difference. 

    I believe the EEM auth / validation actually happens after the jobs matching the pattern are retrieved from the database, so unless the leading '%' is actually a literal in the job name, EEM is happy to allow based on the resolved/matched job names. 

    I'd request Broadcom engineers to review and help here. @Venkateshwar Goud Badrigari any suggestions here please?




  • 4.  RE: Oracle 19c chokes when a wildcarded autorep with a leading % is executed

    Posted 3 days ago
    Hi Chandrasekaran

    You saved the day for us many years ago with a production down issue. Many thanks again.

    Interesting find. I’d push this issue as a defect with Broadcom (maybe the fix is delivered as a new autorep binary?). Internally, communicate the need to stop using autorep that way until a fix is provided or maybe a wrapper script around autorep to ignore % prefix.

    Take care
    Bryan

    Sent from my iPhone

    On Feb 6, 2025, at 10:56 PM, Chandrasekaran Venkataraman via Broadcom <mail@broadcom.com> wrote:

    
    Hi Antony, Tried that as well, no difference. I believe the EEM auth / validation actually happens after the jobs matching the pattern are... -posted to the "AutoSys Workload Automation" community
    [Broadcom] <https: urldefense.com v3 __https: community.broadcom.com__;!!h9nueqsq!-f8pfovnabr77bq-ml-p0uk84ew5qcpdbjveirzcxakic-3rdt6dumz107ln2n6vpqlyuyy-esjsicsuuw$>
    AutoSys Workload Automation<https: urldefense.com v3 __https: community.broadcom.com communities community-home digestviewer?communitykey=45e0ea8b-1184-409f-80eb-26bf98cd2e61__;!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsKtF42xGA$>
    Post New Message<mailto:broadcom-workloadautomation1@connectedcommunity.org>
    Re: Oracle 19c chokes when a wildcarded autorep with a leading % is executed<https: urldefense.com v3 __https: community.broadcom.com discussion oracle-19c-chokes-when-a-wildcarded-autorep-with-a-leading-is-executed*bmaaa52a8e-9495-404b-801f-0194de8a122a__;iw!!h9nueqsq!-f8pfovnabr77bq-ml-p0uk84ew5qcpdbjveirzcxakic-3rdt6dumz107ln2n6vpqlyuyy-esiba0irng$>
    Reply to Group<mailto:broadcom_workloadautomation1_aaa52a8e-9495-404b-801f-0194de8a122a@connectedcommunity.org?subject=re:%20oracle%2019c%20chokes%20when%20a%20wildcarded%20autorep%20with%20a%20leading%20%%20is%20executed> Reply to Sender<https: urldefense.com v3 __https: community.broadcom.com communities all-discussions postreply?messagekey=aaa52a8e-9495-404b-801f-0194de8a122a&ListKey=e54fa869-18a3-47bb-8df1-2a8dce19b42b&SenderKey=4f167663-7826-4380-88ff-8c9c46fb4cc4__;!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsLr0YBE9w$>
    [Chandrasekaran Venkataraman]<https: urldefense.com v3 __https: community.broadcom.com network members profile?userkey=4f167663-7826-4380-88ff-8c9c46fb4cc4__;!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsK1P2aNjA$>
    Feb 6, 2025 10:56 PM
    Chandrasekaran Venkataraman<https: urldefense.com v3 __https: community.broadcom.com network members profile?userkey=4f167663-7826-4380-88ff-8c9c46fb4cc4__;!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsK1P2aNjA$>

    Hi Antony,

    Tried that as well, no difference.

    I believe the EEM auth / validation actually happens after the jobs matching the pattern are retrieved from the database, so unless the leading '%' is actually a literal in the job name, EEM is happy to allow based on the resolved/matched job names.

    I'd request Broadcom engineers to review and help here. @Venkateshwar Goud Badrigari<https: urldefense.com v3 __https: community.broadcom.com network members profile?userkey=1f159500-fe60-403a-930d-50ad452419d3__;!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsKKtCDytg$> any suggestions here please?

    Reply to Group Online<https: urldefense.com v3 __https: community.broadcom.com communities all-discussions postreply?messagekey=aaa52a8e-9495-404b-801f-0194de8a122a&ListKey=e54fa869-18a3-47bb-8df1-2a8dce19b42b__;!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsKcdBTgMA$> Reply to Group via Email<mailto:broadcom_workloadautomation1_aaa52a8e-9495-404b-801f-0194de8a122a@connectedcommunity.org?subject=re:%20oracle%2019c%20chokes%20when%20a%20wildcarded%20autorep%20with%20a%20leading%20%%20is%20executed> View Thread<https: urldefense.com v3 __https: community.broadcom.com discussion oracle-19c-chokes-when-a-wildcarded-autorep-with-a-leading-is-executed*bmaaa52a8e-9495-404b-801f-0194de8a122a__;iw!!h9nueqsq!-f8pfovnabr77bq-ml-p0uk84ew5qcpdbjveirzcxakic-3rdt6dumz107ln2n6vpqlyuyy-esiba0irng$> Recommend<https: urldefense.com v3 __https: community.broadcom.com:443 discussion oracle-19c-chokes-when-a-wildcarded-autorep-with-a-leading-is-executed?messagekey=aaa52a8e-9495-404b-801f-0194de8a122a&cmd=rate&cmdarg=add*bmaaa52a8e-9495-404b-801f-0194de8a122a__;Iw!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsJXoZ2CJw$> Forward<https: urldefense.com v3 __https: community.broadcom.com communities all-discussions forwardmessages?messagekey=aaa52a8e-9495-404b-801f-0194de8a122a&ListKey=e54fa869-18a3-47bb-8df1-2a8dce19b42b__;!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsKVcw-CCw$> Flag as Inappropriate<https: urldefense.com v3 __https: community.broadcom.com discussion oracle-19c-chokes-when-a-wildcarded-autorep-with-a-leading-is-executed?markappropriate=aaa52a8e-9495-404b-801f-0194de8a122a*bmaaa52a8e-9495-404b-801f-0194de8a122a__;Iw!!H9nueQsQ!-F8pfOVNAbr77BQ-Ml-P0Uk84EW5QcpdbJveirZcxAkic-3rdt6DUmz107lN2N6VPqlyUyy-EsIP996Oig$>



  • 5.  RE: Oracle 19c chokes when a wildcarded autorep with a leading % is executed

    Posted 2 days ago
    Edited by Pothen Verghese 2 days ago

    Hi Chandru,

    I don't think it is possible to block wildcards like %, *, ? using EEM. We have policies that restrict as-job/as-list to certain alphanumeric and spl chars only and no user can create or fetch jobs if the names don't match those that are allowed under EEM. Even if the admin user tries to create a job using alphabets that are not allowed under EEM, the pop up is as below:

    But wildcards are different and it seems that they cannot be blocked. Only the characters supported as part of AutoSys object names can be blocked.

    I believe the statement 'EEM validation happens after jobs matching pattern are retrieved from the database' is not true.  Only on successful EEM validation  one can read/write/delete etc at the database level. May be someone from AutoSys dev can confirm.

    Regards

    Pothen




  • 6.  RE: Oracle 19c chokes when a wildcarded autorep with a leading % is executed

    Posted 4 days ago
    Hi Chandru,
     
    When we had similar issue, our DBA had to update the below DB parameter. The option was not set and I think we set this at db init level and bounce the Databases.
     
    filesystemio = setall



  • 7.  RE: Oracle 19c chokes when a wildcarded autorep with a leading % is executed

    Posted 3 days ago

    Hi Emmanuel, 

    DBA says FILESYSTEMIO_OPTIONS is already set to "setall" and it's been that way since Oracle 8i :-)

    Thank you,

    Chandru V