Results 1 to 9 of 9
  1. #1
    Join Date
    May 2010
    Posts
    21

    Post Unanswered: Error -583, SQLState: 42845

    I have a rather long query that was so long I put some of the subquery logic into an SQL function for readability and because the "format sql" function in the program we use--DBVisualizer 6.0.15--was beginning to error out. So I got the query complete and tried to run it but got error 583 state 42845. I dropped the function and redefined it as NO EXTERNAL ACTION, but the error remained. I even tried defining the function as deterministic, which I do not believe it is because the table the query comes from can change from time to time so that the function will return different values from the same input values, but the error remained. There is one call in count_lessgrades but it is to a deterministic function that always returns the same values for the same inputs.

    What can I do to resolve the error? I can replace the code, but I should be able to place the subqueries in a function. I should then be able to place my overall query in a view.

    Code:
    CREATE FUNCTION A.COUNT_LESSGRADES
        (
            PARM_YEAR INT, 
            PARM_PER_ID VARCHAR(20), 
            PARM_LOC_ID VARCHAR(20), 
            PARM_SERVICE_ID VARCHAR(20), 
            PARM_NUM_OF_PARTS INT, 
            PARM_PART_NUM INT, 
            PARM_STANDARD INT
        )
    RETURNS INT 
    DETERMINISTIC
    NO EXTERNAL ACTION
    RETURN
    SELECT 
        COUNT(BISDCUST.GRADE(SG_IN.STU_GRD_TYP_GRP_SEC_V_VCGRDVAL)) 
    FROM 
        STUDENT S
    INNER JOIN 
        STUDENT_CLASS SC
        ON 
        ( 
            S_IN.PER_ID = SC.PER_ID 
        ) 
    INNER JOIN 
        STUDENT_GRD_TYP_GRP_SEC_VAL SG //STUDENT GRADE
        ON 
        ( 
            SC.PER_ID                      = SG.PER_ID 
            AND SC.LOC_ID                  = SG.LOC_ID 
            AND SC.DIST_COURSE_ID          = SG.DIST_COURSE_ID 
            AND SC.LOC_CRS_SECTION_ID      = SG.LOC_CRS_SECTION_ID 
            AND SC.STUDENT_CAL_ID          = SG.STUDENT_CAL_ID 
            AND SC.STU_GRD_GROUP_ID        = SG.STU_GRD_GROUP_ID 
            AND SC.DIST_COURSE_FISCAL_YEAR = SG.STUDENT_CAL_FISCAL_YEAR 
        ) 
    INNER JOIN 
        COURSE_CATALOG CC 
        ON 
        ( 
            SC.DIST_COURSE_ID              = CC.DIST_COURSE_ID 
            AND SC.DIST_COURSE_FISCAL_YEAR = CC.DIST_COURSE_FISCAL_YEAR 
        ) 
    INNER JOIN 
        LOCATION_CRS_SECTION_TERM LCST_IN 
        ON 
        ( 
            SC_IN.LOC_ID                      = LCST_IN.LOC_ID 
            AND SC_IN.DIST_COURSE_ID          = LCST_IN.DIST_COURSE_ID 
            AND SC_IN.DIST_COURSE_FISCAL_YEAR = LCST_IN.DIST_COURSE_FISCAL_YEAR 
            AND SC_IN.STUDENT_CAL_ID          = LCST_IN.STUDENT_CAL_ID 
            AND SC_IN.LOC_CRS_SECTION_ID      = LCST_IN.LOC_CRS_SECTION_ID 
            AND SC_IN.STUDENT_CAL_TERM_TYPE   = LCST_IN.STUDENT_CAL_TERM_TYPE 
        ) 
    WHERE 
        SC_IN.dist_course_fiscal_year                                                                    = PARM_YEAR 
        AND SC_IN.PER_ID                                                                                 = PARM_PER_ID 
        AND SC_IN.LOC_ID                                                                                 = PARM_LOC_ID 
        AND DCC_IN.RFDS_SERVICE_ID_CODE                                                                  = PARM_SERVICE_ID
        AND BISDCUST.COURSESEQCODE(DCC_IN.DIST_COURSE_NUM_OF_PARTS, LCST_IN.LOC_CRS_SEC_TAUGHT_PART_NUM) = BISDCUST.COURSESEQCODE ( PARM_NUM_OF_PARTS, PARM_PART_NUM) 
        AND SG_IN.GRADE_TYPE_NM                                                                         in ('1st Six Weeks', '2nd Six Weeks', '3rd Six Weeks', '4th Six Weeks', '5th Six Weeks', '6th Six Weeks') 
        AND BISDCUST.GRADE(SG_IN.STU_GRD_TYP_GRP_SEC_V_VCGRDVAL)                                         < PARM_STANDARD
    EXAMPLE:
    Code:
    SELECT COUNT_LESSGRADES(LCST.DIST_COURSE_FISCAL_YEAR, S.PER_ID, SC.LOC_ID, DCC.RFDS_SERVICE_ID_CODE, 3, 1, 70)    != 0 
    FROM INNER JOIN 
        STUDENT_GRD_TYP_GRP_SEC_VAL SG 
        ON 
        ( 
            SC.PER_ID                      = SG.PER_ID 
            AND SC.LOC_ID                  = SG.LOC_ID 
            AND SC.DIST_COURSE_ID          = SG.DIST_COURSE_ID 
            AND SC.LOC_CRS_SECTION_ID      = SG.LOC_CRS_SECTION_ID 
            AND SC.STUDENT_CAL_ID          = SG.STUDENT_CAL_ID 
            AND SC.STU_GRD_GROUP_ID        = SG.STU_GRD_GROUP_ID 
            AND SC.DIST_COURSE_FISCAL_YEAR = SG.STUDENT_CAL_FISCAL_YEAR 
        ) 
    INNER JOIN 
        DISTRICT_COURSE_CATALOG DCC 
        ON 
        ( 
            SC.DIST_COURSE_ID              = DCC.DIST_COURSE_ID 
            AND SC.DIST_COURSE_FISCAL_YEAR = DCC.DIST_COURSE_FISCAL_YEAR 
        ) 
    INNER JOIN 
        LOCATION_CRS_SECTION_TERM LCST 
        ON 
        ( 
            SC.LOC_ID = LCST.LOC_ID 
        ) 
        AND 
        ( 
            SC.DIST_COURSE_ID              = LCST.DIST_COURSE_ID 
            AND SC.DIST_COURSE_FISCAL_YEAR = LCST.DIST_COURSE_FISCAL_YEAR 
            AND SC.STUDENT_CAL_ID          = LCST.STUDENT_CAL_ID 
            AND SC.LOC_CRS_SECTION_ID      = LCST.LOC_CRS_SECTION_ID 
            AND SC.STUDENT_CAL_TERM_TYPE   = LCST.STUDENT_CAL_TERM_TYPE 
        ) 
    WHERE 
        SC.dist_course_fiscal_year = bisdcust.CURRENTFISCAL() 
        AND 
        ( 
            SG.GRADE_TYPE_NM LIKE '1st Six Weeks' 
            or SG.GRADE_TYPE_NM LIKE '2nd Six Weeks' 
            or SG.GRADE_TYPE_NM LIKE '3rd Six Weeks' 
            or SG.GRADE_TYPE_NM LIKE '4th Six Weeks' 
            or SG.GRADE_TYPE_NM LIKE '5th Six Weeks' 
            or SG.GRADE_TYPE_NM LIKE '6th Six Weeks' 
            or SG.GRADE_TYPE_NM LIKE 'Average 1' 
            or SG.GRADE_TYPE_NM LIKE 'Average 2' 
            or SG.GRADE_TYPE_NM LIKE 'Yearly' 
        )
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050" with level identifier "03010107". Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack "0". Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

  2. #2
    Join Date
    May 2010
    Posts
    21
    This is not the entire query, but it is the relevant parts to the error.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by jkuyken View Post
    some of the subquery logic into an SQL function for readability
    For me CTE's (you know: the WITH clause) are very suitable for that purpose
    Quote Originally Posted by jkuyken View Post
    the "format sql" function in the program we use--DBVisualizer 6.0.15--
    try this: Instant SQL Formatter

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why didn't you specify "NOT DETERMINISTIC" and "EXTERNAL ACTION"?
    (Defaults are NOT DETERMINISTIC and EXTERNAL ACTION)

    My guess was that BISDCUST.GRADE and/or BISDCUST.COURSESEQCODE were defined NOT DETERMINISTIC and/or EXTERNAL ACTION(or used default).
    Last edited by tonkuma; 05-19-10 at 08:01. Reason: Add "EXTERNAL ACTION"

  5. #5
    Join Date
    May 2010
    Posts
    21

    Thanks.

    I went through every function called and defined each as 'DETERMINISTIC', 'NO EXTERNAL ACTION'. The 'no external action' is true, and the deterministic is technically untrue but true in practice because users are off the system after 5pm. Is it possible to temporarily set a tableset as read only at the beginning of a query until it ends? This query is generating derived demographic information from other system tables.

  6. #6
    Join Date
    May 2010
    Posts
    21
    I need one of those smiley faces that looks like someone just punched them.

    Resolved all the errors and ran the query. It returned sqlcode:-101 sqlstate:54001 THE STATEMENT IS TOO LONG OR COMPLEX.

    How to resolve that?

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You have an SQL statement that is greater than the limit?
    Maximum total length of an SQL statement (in bytes) 2 097 152

    Have you considered shorter table and column names?
    User response
    Either:
    Break the statement up into shorter or less complex SQL statements.
    Increase the size of the statement heap (stmtheap) in the database configuration file.
    Reduce the number of check or referential constraints involved in the statement or reduce the number of indexes on foreign keys.
    Reduce the number of triggers involved in the statement.
    Federated system users: determine which data source is failing the statement (see the Troubleshooting Guide for procedures to follow to identify the failing data source) and determine the cause of the rejection. If the rejection is coming from the federated server, increase the size of the statement heap (stmtheap) in the database configuration file.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another consideration may be SQL user defined functions used in the query.

    Trying to simplify the UDFs might help you.

  9. #9
    Join Date
    May 2010
    Posts
    21

    Resolved.

    I ended up replacing the function calls with 'with' where I could. I also, since all calls where coming from the same 4 unioned tables used with to name them source. Additionally, I did processing in the source section to generate boolean-like flags. This greatly simplified the remaining subqueries and logic. The resulting code was 600 lines as formatted by the white-space excessive DBVisualizer. Running a few validation checks now, but the remaining issues should be simple.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •