not authorized to use SQL insert Statement

[rbaudchon]

I would like to use SQL Insert or Update statement in a query (using STRQM), but my system says that I'm not authorized to use INSERT or UPDATE. I can only use SELECT.
Using interactive SQL (STRSQL), I 'm able to use INSERT or UPDATE.

Where can I give the authorization to the userid ?
I'm using an iseries V5R3

[jamief]

Re: not authorized to use SQL insert Statement

I would guess you dont have authorization to the file you are trying to update.
not the actuall function(s) of update and insert.
to test this create a new file in your library and try to insert a record into that one.

please get back to us.

[rbaudchon]

Re: not authorized to use SQL insert Statement

I have created a file, and I have got all the authorization on the objet.
I'm able to update and insert new values using STRSQL. But still not working when using a query, I have got always the same message. You are not authorized to use INSERT.
I have tried with differents account (qsecofr) and it is the same message.

I can only use SELECT.
UPDATE, DELETE, INSERT is forbiden.

[DeadManWalks]

Re: not authorized to use SQL insert Statement

Do this and give us the results.
1) STRQM
2) 10
3) F10
4) Enter
This is what I see;

 Allowed SQL Statements:                                                      
                                                                             
SQL                                      SQL                                 
Statement                                Statement                           
ALTER TABLE                              CREATE PROCEDURE                    
CALL                                     CREATE TABLE                        
COMMENT ON                               CREATE TRIGGER                      
COMMIT                                   CREATE VIEW                         
CREATE ALIAS                             DECLARE GLOBAL TEMPORARY TABLE      
CREATE COLLECTION                        DELETE                              
CREATE DISTINCT TYPE                     DROP ALIAS                          
CREATE FUNCTION                          DROP COLLECTION                     
CREATE INDEX                             DROP DISTINCT TYPE  DROP FUNCTION    
                       INSERT             
DROP INDEX                               LABEL ON           
DROP PACKAGE                             LOCK TABLE         
DROP PROCEDURE                           RELEASE SAVEPOINT  
DROP ROUTINE                             RENAME             
DROP TABLE                               REVOKE             
DROP TRIGGER                             ROLLBACK           
DROP VIEW                                SAVEPOINT          
GRANT                                    SELECT                 SET PATH         
SET SCHEMA       
SET TRANSACTION  
UPDATE 

what does yours say.

[Red]

Re: not authorized to use SQL insert Statement

It might be worth posting your code to see if anyone can spot a syntax error.

Also, check your file specifications in the program to see if itis set update/add.

Also, check the file itself and see if it is an input only file

[Andrew K]

Re: not authorized to use SQL insert Statement

DeadManWalks is right I would guess. The default in the QM profile is 'Select' only, so you need to go into your QM profile (change mode) and change ' Select allowed SQL statements' to 'Y' and add those you need (F21 for all).

[rbaudchon]

Re: not authorized to use SQL insert Statement

Ok it works, many thanks to all.
It is exactly what Deadman Wals and Andrew have said.
1)STRQM
2)10
3)select QM profile
4)2 change mode
5)set 'select allows SQL statements' to 'Y'
6)change to 1 SQL command you want to use