Блог

Ниже показаны сценарии sql для ibm. Я новичок в IBMi том, что не могу понять, чего они хотят.

   Formatting error encountered...   Line 85: "**" expected after this token   Failed to format 1 statements 
 

 /*  Produce outfile first via this command on IBMi:*/;
CL: DSPOBJD OBJ(SCRUBLIB/*ALL) OBJTYPE(*ALL) DETAIL(*BASIC) OUTPUT(*OUTFILE) OUTFILE(HERAPERM/SCRUBLIB);


/* Build objects in SCRUBLIB not in Activity from Journal Dump summary file */;
 

 --drop table HERAPERM.SCRUBLITST;
    CREATE TABLE HERAPERM.SCRUBLITST AS
            (
/* Build Activity from Journal Dump summary file */
                WITH scrub_activity AS (
                         SELECT joobj,
                                jouser,
                                entry_count,
                                first_entry,
                                last_entry,
                                CASE
                                    WHEN
                                        jocode = 'J' -- start / end journalling.
                                        OR jouser IN ('JGP212', 'KXK632', 'DJY881', 'S_DBAADMIN', 
      'SYSADMIN', 'S_PRDSUPT')
                                        THEN 'admin'
                                    WHEN jouser IN ('QUICK', 'QSYS')
                                        AND -- ignore replication amp; other background activity
                                        ((jocode = 'F'
                                                AND joentt IN ('CB', 'CL', 'OP'))
                                                -- OP = open, CL = closed, CB = member changed (generally 
     for stats info)
                                            OR (jocode = 'D'
                                                AND joentt IN ('DH', 'DW')) -- save activitiy
                                            ) THEN 'system'
                                    WHEN
                                        jouser IN ('MISRBTAZ',
                                            'MISRBTEA', 'MISRBTCZ', 'MISRBT', 'MISRBTNW', 'MISRBTCT', 
       'MISRBTLR', 'MISRBTPA',
                                            'MISRBTUS', 'MISRBTCW', 'MISRBTLF', 'MISRBTCA', 'MISRBTMS', 
      'MISRBTFT')
                                        THEN 'production jobs'
                                    WHEN
                                        jouser IN ('TURNOVER',
                                            'PGMRADMIN', 'MXE325', 'MXE325', 'ELP847', 'KVP548', 
    'WMC577', 'CKK315', 'TDM450',
                                            'AAG907', 'RAV995', 'AGG582', 'GDD759', 'CDJ667', 'MBB128', 
    'JHH085', 'AAA249',
                                            'AGC549', 'JID202', 'V_KNN648', 'FSS256', 'DHT776', 'XPP203', 
    'RNF185', 'KRR842',
                                            'KOT500', 'RKK970', 'KNN152', 'SRR886', 'PPP228', 'V_GVV273', 
    'JFF869', 'KSS648',
                                            'KKK588', 'JFF449', 'JAC000', 'DWW693', 'SSS865', 'RPK746', 
    'V_FSS633', 'SAA508')
                                        THEN 'developer activity'
                                    ELSE 'other'
                                END AS activity_type
                             FROM techcmds.scrubjrn1
                             WHERE joobj <> '' -- only interested in object changes, not receiver 
    switches, etc.
                                    AND SUBSTR(last_entry, 1, 2) = to_char(current_date - 2 MONTH, 'MM')
                                   -- Only objects activity (2 months ago)
                                   OR joobj <> '' -- only interested in object changes, not receiver 
    switches, etc.
                                   AND SUBSTR(last_entry, 1, 2) = to_char(current_date - 1 MONTH, 'MM')
                                   -- Only objects activity (1 month ago)
                                   OR joobj <> '' -- only interested in object changes, not receiver 
    switches, etc.
                                   AND SUBSTR(last_entry, 1, 2) = to_char(current_date, 'MM')
                                   -- Only objects activity (Current month)
                     ),
                     scrub_activity_sum AS (
                         SELECT joobj,
                                activity_type,
                                SUM(entry_count) entry_count,
                                MIN(first_entry) first_entry,
                                MAX(last_entry) last_entry
                             FROM scrub_activity
                             GROUP BY joobj,
                                      activity_type
                     )
    /* Compare SCRUBLIB objects against journal activity to find objects in Scrublib*/
    /* that have not been opened/closed/etc for prod purposes */
    /* Scrublib Inventory created from EndDr SQL Analysis script   */
                    SELECT x.ODOBNM,
                           x.ODOBTP,
                           x.ODOBAT,
                           x.ODCDAT,
                           x.ODUDAT
                        FROM HERAPERM.SCRUBLIB AS x
                        WHERE x.ODOBNM NOT IN (SELECT y.JOOBJ
                                      FROM scrub_activity_sum AS y
                                      WHERE activity_type NOT IN ('admin', 'system')
    /* Filters objects created in current year and less than filter month */
                                            AND y.JOOBJ IS NULL)
                              AND x.ODCRTS = 'ENDEAVOR'
                              /* Ignore objects created on EndDr/DPITest for Endeavor cleanup */
                              /* 12-2020 */
                              AND x.ODOBNM <> 'QDFTJRN' /* Ignore journal manager dtaara */
                              AND x.ODOBTP <> '*QMQRY'/* Ignore Query Objects *//* 12-2020 */
                              AND x.ODOBTP <> '*QRYDFN'/* Ignore Query Objects *//* 12-2020 */
                               (SELECT x.ODOBNM,
                                         x.ODOBTP,
                                         x.ODOBAT,
                                         x.ODCDAT,
                                         x.ODUDAT
                                      FROM HERAPERM.SCRUBLIB AS x
                                      WHERE x.ODOBNM NOT IN (SELECT Y.ODOBNM
                                                  FROM HERAPERM.SCRUBLIST AS y)))
            WITH DATA
;