CODEWITHSUNDEEP
ibm-midrangedb2-400rpgle
Javed Ali
Javed Ali

Reputation: 1560

DB2 SQLCode -7008

I am using an sqlrgple program to insert a select number of records into a file which I then output to the user. See code Snippet below

// Build SQL statement according to the parameters passed

        if rpttype = 'O';

          sqlstmt = 'Insert into fqrylibpgm/sblbrpt ' +
            '(shclno, shscdt, shcmdt, shcust, ' +
            'ttlonl, ttlofl, ttltvl) ' +
            'Select shclno, shscdt, shcmdt, shcust, '+
            'coalesce(sum(cast(((tbontm * 0.01) * tbonbr)' + 
            ' as dec(8,2))),0), ' +
            'coalesce(sum(cast(((tboftm * 0.01) * tbofbr)' +  
            ' as dec(8,2))),0), ' +
            'coalesce(sum(cast(((tbtvtm * 0.01) * tbtvbr)' +  
            ' as dec(8,2))),0) ' +
            'from r50files/sbschd '+
            'left join r50modsdta/sbsctc on tbcmp = shcmp and ' +
            'tbcust = shcust and tbclno = shclno ';

        elseif rpttype = 'C';

          sqlstmt = 'Insert into fqrylibpgm/sblbrpt ' +
            '(shclno, shscdt, shcmdt, shcust, ' +
            'ttlonl, ttlofl, ttltvl) ' +
            'Select shclno, shscdt, shcmdt, shcust, '+
            'coalesce(sum(cast(((tbontm * 0.01) * tbonbr)' +  
            ' as dec(8,2))),0), ' +
            'coalesce(sum(cast(((tboftm * 0.01) * tbofbr)' +  
            ' as dec(8,2))),0), ' +
            'coalesce(sum(cast(((tbtvtm * 0.01) * tbtvbr)' +  
            ' as dec(8,2))),0) ' +
            'from r50files/sbhshd '+
            'left join r50modsdta/sbhstc on tbcmp = shcmp and ' +
            'tbcust = shcust and tbclno = shclno ';

        endif;

        // Only for Rentals location

        sqlstmt += 'where shloc = 1202 ';

        // Scheduled Date Filter

        sqlstmt += 'and (shscdt >= ' + %char(scdtfr) +
                   ' and shscdt <= ' + %char(scdtto) + ') ';

        //Completed Date Filter

        sqlstmt += 'and (shcmdt >= ' + %char(cmdtfr) +
                   ' and shcmdt <= ' + %char(cmdtto) + ') ';

        // Group Clause to get Sum

        sqlstmt += ' group by shclno, shscdt, shcmdt, ' +
                   ' shcust ';

        sqlstmt += ' order by shclno'; 

        // Execute SQL Insert statement

        exec sql prepare sqlsel from :sqlstmt;
        exec sql execute sqlsel;

        // Get SQL return codes (use to debug)

        exec sql GET DIAGNOSTICS CONDITION 1
                 :rsqlcode = DB2_RETURNED_SQLCODE,
                 :rmsgtext = MESSAGE_TEXT;

        exec sql commit;

The problem is when I run this program, I get a sqlcode of -7008 and a message text saying "SBLBRPT NOT VALID FOR OPERATION"

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/codes/src/tpc/n7008.html

According to the docs I am supposed to get a reason code following this but I don't.

When I debug the program to ensure the query is correct. I take the value of sqlstmt before it is executed and I try that statement in STRSQL as is and it works fine.

The file SBLBRPT is in the library list as well.

I have done programs like this before and haven't come across this error yet and I am not sure how to fix it. Any assistance would be greatly appreciated

Upvotes: 0

Views: 3199

Answers (1)

Charles
Charles

Reputation: 23823

Reason code is in the job log...

Likely, you've taken the defaults on the CRTSQLRPGI command, namely COMMIT(*CHG) and the table (file) in question is not journalled.

Add a exec sql set option commit=*none; to your program, or add with nc to the statement...or change the option at compile time.

Upvotes: 5

Related Questions