Symantec IGA

 View Only
Expand all | Collapse all

Stuck In Progress Tasks / Events & Garbage Collection

  • 1.  Stuck In Progress Tasks / Events & Garbage Collection

    Posted Feb 14, 2012 04:13 PM

    [color=#ff0000]***For some reason this post was marked as a draft and attachments were lost during the MyCA upgrade *** Will need to search for attachments and update it again with the supporting information. (pending)[color]

    Tasks can become stuck in progress for a variety of reasons, most notably, view submitted tasks shows the task / event as stuck in progress or the associated workflow job is left in an unapproved state.

    Task Persistence Database

    TP Database structure, which is leveraged by View submitted tasks feature within the IME, involves 5 differet DB tables:


    1. event_object12_5
    2. event12_5
    3. lock12_5
    4. object12_5
    5. tasksession12_5

    The 2 tables that report the state of the task/event are:

    event12_5 and tasksession12_5. On both tables there is a field called state which will indicate at the task/event state.

    Here is a list of the possible states:

    BEGIN_STATE = 0x00; // 0
    PRE_STATE = 0x01; // 1
    INVALID_STATE = 0x02; // 2
    PENDING_STATE = 0x04; // 4
    EXECUTING_STATE = 0x08; // 8
    APPROVED_STATE = 0x10; // 16
    REJECTED_STATE = 0x20; // 32
    POST_STATE = 0x40; // 64
    COMPLETED_STATE = 0x80; // 128
    CANCELLED_STATE = 0x100; // 256
    MARK_FOR_DELETION_STATE = 0x200; // 512
    UNKNOWN_STATE = 0x300; // 768
    INITIAL_STATE = 0x400; // 1024
    PRIMARY_PENDING_STATE = 0x800; // 2048
    PRIMARY_COMPLETE_STATE = 0x1000; // 4096
    SECONDARY_PENDING_STATE = 0x2000; // 8192
    AUDIT_STATE = 0x4000; // 16384

    So state 2 is failed as you can see INVALID_STATE.

    All the task states are mapped to 'pending' and 'in progress' except for 'completed', 'cancelled' and 'rejected'.

    I.e., "In Progress" or "Pending" = TP states 0, 1, 2, 4, 8, 16, 64.

    'Failed' tasks do not have a unique state because the tasks can fail during different events within the task. Failed tasks will have the last logged state at the time of the failure which could be one of the pending or in process states.

    It's important to keep your task persistence database lean and in peak performance, by regularly implementing OOTB garbage collection procedures here:

    If you've neglected to run garbage collection on your submitted tasks within the task persistence database, it's possible that the OOTB garbage cleanup procedure can easily become overwhelmed.. If your garbage collection is taking exceedingly long to complete, you may want to consider implementing this and INITIATION_DATE <{ts '2012-01-04 00:00:00'} This will need to be changed to reflect the current date range when you run this SQL statement.

    Additionally attached to this thread is the garbage collection procedure for workpoint specifically.

    Selecting only the parent jobs isn't necessary because the Workpoint delete stored procs only delete the jobs where the parent is marked to be deleted. For instance if you update a sub-proc and not the parent, the sub-proc will not be deleted.

    Please respond with any questions or concerns.
    Thank you.
    Regards,

    Chris Thomas
    CA Technologies
    Principal Support Engineer
    Identity Manager Reporting Expert
    Tel: +1-631-342-4360
    Chris.Thomas@ca.com



  • 2.  RE: Stuck In Progress Tasks / Events & Garbage Collection

     
    Posted Nov 14, 2013 08:08 PM
    Chris_Thomas:
    [color=#ff0000]***For some reason this post was marked as a draft and attachments were lost during the MyCA upgrade *** Will need to search for attachments and update it again with the supporting information. (pending)[/color] Tasks can become stuck in progress for a variety of reasons, most notably, view submitted tasks shows the task / event as stuck in progress or the associated workflow job is left in an unapproved state. Task Persistence Database TP Database structure, which is leveraged by View submitted tasks feature within the IME, involves 5 differet DB tables: 1. event_object12_5 2. event12_5 3. lock12_5 4. object12_5 5. tasksession12_5 The 2 tables that report the state of the task/event are: event12_5 and tasksession12_5. On both tables there is a field called state which will indicate at the task/event state. Here is a list of the possible states: BEGIN_STATE = 0x00; // 0 PRE_STATE = 0x01; // 1 INVALID_STATE = 0x02; // 2 PENDING_STATE = 0x04; // 4 EXECUTING_STATE = 0x08; // 8 APPROVED_STATE = 0x10; // 16 REJECTED_STATE = 0x20; // 32 POST_STATE = 0x40; // 64 COMPLETED_STATE = 0x80; // 128 CANCELLED_STATE = 0x100; // 256 MARK_FOR_DELETION_STATE = 0x200; // 512 UNKNOWN_STATE = 0x300; // 768 INITIAL_STATE = 0x400; // 1024 PRIMARY_PENDING_STATE = 0x800; // 2048 PRIMARY_COMPLETE_STATE = 0x1000; // 4096 SECONDARY_PENDING_STATE = 0x2000; // 8192 AUDIT_STATE = 0x4000; // 16384 So state 2 is failed as you can see INVALID_STATE. All the task states are mapped to 'pending' and 'in progress' except for 'completed', 'cancelled' and 'rejected'. I.e., "In Progress" or "Pending" = TP states 0, 1, 2, 4, 8, 16, 64. 'Failed' tasks do not have a unique state because the tasks can fail during different events within the task. Failed tasks will have the last logged state at the time of the failure which could be one of the pending or in process states. It's important to keep your task persistence database lean and in peak performance, by regularly implementing OOTB garbage collection procedures [url=https://support.ca.com/cadocs/0/CA%20Identity%20Manager%20r12%205%20SP8-ENU/Bookshelf_Files/HTML/1031429_1.html?zoom_highlight=clean%2Btask%2Bpersistence]here[/url]: If you've neglected to run garbage collection on your submitted tasks within the task persistence database, it's possible that the OOTB garbage cleanup procedure can easily become overwhelmed.. If your garbage collection is taking exceedingly long to complete, you may want to consider implementing this [url=https://communities.ca.com/c/message_boards/get_message_attachment?messageId=97528102&attachment=Taskpersistence+cleanup_update4.docx, but would strongly encourage you to only leverage this feild developed utility on your development / test environment, as it's NOT fit for production use. Within Workpoint tables you can determine if a Job is in progress by executing the following query: select proci_id, proci_db from wp_proci where proc_state_id = 7 The above query will retrieve the IDs of all created jobs. When you want to find ‘Pending’ workflow enabled jobs that are stuck in progress, then this is the query you need. A date range where INITIATION_DATE > {ts '2012-01-03 12:00:00'} and INITIATION_DATE <{ts '2012-01-04 00:00:00'} This will need to be changed to reflect the current date range when you run this SQL statement. Additionally attached to this thread is the garbage collection procedure for workpoint specifically. Selecting only the parent jobs isn't necessary because the Workpoint delete stored procs only delete the jobs where the parent is marked to be deleted. For instance if you update a sub-proc and not the parent, the sub-proc will not be deleted. Please respond with any questions or concerns. Thank you. Regards, Chris Thomas CA Technologies Principal Support Engineer Identity Manager Reporting Expert Tel: +1-631-342-4360 Chris.Thomas@ca.com


    Thanks for rescuing this tip and posting Chris!

     



  • 3.  RE: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Nov 22, 2013 04:52 PM

    Three files attached. 



  • 4.  Re: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Dec 06, 2018 06:07 PM

    Any update available for CA Identity Suite/Manager 14.2?

     

    BR, Gabriele



  • 5.  RE: Re: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Nov 13, 2019 03:01 AM
    Hi Gabriele,

    Did you receive any feedback around this? I have the issue and I urgently need to cleanup the task persistence DB on MSSQL server.

    Kind Regards
    Tav



    ------------------------------
    Snr IAM Architect
    I'CURITY SOLUTIONS
    ------------------------------



  • 6.  RE: Re: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Nov 13, 2019 03:35 AM
    What version of CA IM are you using ? 
    In V14.3 Under CA IM(UserConsole)-> System, there is a task called "Cleanup Submitted task" ,which u can trigger the clean up via CA IM UI.


    Alternatively, u can also trigger the clean up via SQL procedure using SQL mgmt studio


  • 7.  RE: Re: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Nov 13, 2019 03:49 AM
    Hi 

    I'm using version 14.1.
    Using the CA IM UI is taking forever... Processing about 1100 transaction in 5 hours :(
    I need a SQL Procedure to archive and delete using SQL Management studio however, I don't see any SQL procedures for that. 

    Do you perhaps have some for 14.1?

    Kind Regards
    Tav

    ------------------------------
    Snr IAM Architect
    I'CURITY SOLUTIONS
    ------------------------------



  • 8.  RE: Re: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Nov 13, 2019 04:36 AM
    i only have 14.3 in my env....


  • 9.  RE: Re: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Nov 13, 2019 04:40 AM
    Please share what you have for 14.3 I can tweak that to my needs?

    ------------------------------
    Snr IAM Architect
    I'CURITY SOLUTIONS
    ------------------------------



  • 10.  RE: Re: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Nov 13, 2019 04:52 AM
    can u try access these 2 folder in ur env.
    1)
    /opt/CA/IdentityManager/IAM_Suite/IdentityManager/tools/db/taskpersistence/sqlserver/idm_db_sqlserver.sql

    2) 
    /opt/CA/IdentityManager/IAM_Suite/IdentityManager/tools/samples/TaskPersistence/SQLServer
    there are readme.txt and exec_sqlserver_gc.sql


  • 11.  RE: Re: Stuck In Progress Tasks / Events & Garbage Collection

    Posted Nov 13, 2019 05:07 AM
    Will give that a shot - Thanks a lot.

    ------------------------------
    Snr IAM Architect
    I'CURITY SOLUTIONS
    ------------------------------