Re: ODBC problem (I think) -- MIDRANGE-L

Maybe you can try setting up one :-)

/********************************************************************/

PGM PARM(&STATUS &REQUEST)

DCL VAR(&STATUS) TYPE(*CHAR) LEN(1)    /* ACCEPT OR REJECT         */
DCL VAR(&REQUEST) TYPE(*CHAR) LEN(2000) /* PARAMETER STRUCTURE      */

DCL VAR(&USER)    TYPE(*CHAR) LEN(10)    /* USER ID                */
DCL VAR(&ITYPE)   TYPE(*CHAR) LEN(63)    /* Interface type         */
DCL VAR(&INAME)   TYPE(*CHAR) LEN(127)   /* Interface name         */
DCL VAR(&ILEVEL) TYPE(*CHAR) LEN(63)    /* Interface level        */
DCL VAR(&STMT)    TYPE(*CHAR) LEN(512)   /* Insert statement       */
DCL VAR(&CMD)     TYPE(*CHAR) LEN(512)   /* Comando QSHELL         */

DCL VAR(&RCVVAR) TYPE(*CHAR) LEN(332)
DCL VAR(&RCVLEN) TYPE(*CHAR) LEN(4)
DCL VAR(&FMTNAM) TYPE(*CHAR) LEN(8) VALUE('JOBI0600')
DCL VAR(&JOBNAM) TYPE(*CHAR) LEN(26) VALUE('*')
DCL VAR(&INTJOB) TYPE(*CHAR) LEN(16)
DCL VAR(&JOBNUM) TYPE(*CHAR) LEN(8)

DCL        VAR(&JOB_IP) TYPE(*CHAR) LEN(15)
DCL        VAR(&JOB_NAME) TYPE(*CHAR) LEN(10)
DCL        VAR(&JOB_NBR) TYPE(*CHAR) LEN(6)
DCL        VAR(&JOB_ID) TYPE(*CHAR) LEN(30)
DCL        VAR(&JOB_USER) TYPE(*CHAR) LEN(10)

DCL VAR(&Q)       TYPE(*CHAR) LEN(1) VALUE('''')

   CHGVAR VAR(&USER)   VALUE(%SST(&REQUEST   1 10))
   CHGVAR VAR(&ITYPE) VALUE(%SST(&REQUEST 33 63))
   CHGVAR VAR(&INAME) VALUE(%SST(&REQUEST 96 127))
   CHGVAR VAR(&ILEVEL) VALUE(%SST(&REQUEST 223 63))

   CHGVAR VAR(&STATUS) VALUE('1')   /* Aceptar conexion */

   CHGVAR VAR(%SST(&RCVVAR 129 1)) VALUE('X')
   CHGVAR VAR(%BIN(&RCVLEN)) VALUE(332)

   RTVJOBA    JOB(&JOB_NAME) USER(&JOB_USER) NBR(&JOB_NBR)
   CHGVAR     VAR(&JOB_ID) VALUE(&JOB_NBR *TCAT '/' +
                  *TCAT &JOB_USER *TCAT '/' *TCAT &JOB_NAME)
   CALL       PGM(QSYS/QUSRJOBI) +
                  PARM(&RCVVAR &RCVLEN &FMTNAM &JOBNAM &INTJOB)
   CHGVAR VAR(&JOB_IP) VALUE(%SST(&RCVVAR 308 15))

   CHGVAR VAR(&STMT) VALUE(' INSERT INTO DBLOG.DBINITDATA ' *CAT +
       'Values (CURRENT_TIMESTAMP, ' *CAT &Q *CAT &USER *TCAT &Q +
       *CAT ', ' *CAT &Q *CAT &ITYPE *TCAT &Q *CAT ', ' *CAT &Q +
       *CAT &INAME *TCAT &Q *CAT ', ' *CAT &Q *CAT &ILEVEL +
       *TCAT &Q *CAT ', ' *CAT &Q *TCAT &JOB_ID *TCAT &Q +
       *CAT ', ' *CAT &Q *TCAT &JOB_IP *TCAT &Q *CAT ' ) ')
      RUNSQL     SQL(&STMT) COMMIT(*NONE) NAMING(*SQL)
      MONMSG MSGID(CPF0000)
ENDPGM

/********************************************************************/

And the table DBLOG/DBINITDATA :

ConnectionTime timestamp,
User           CHAR(10),
InterfaceType CHAR(63),
InterfaceName CHAR(127),
InterfaceLevel CHAR(63),
JOB_ID         CHAR(30),
JOB_IP         CHAR(30))

/********************************************************************/

Granting QUSER

GRTOBJAUT OBJ(DBLOG/DBINITDATA) OBJTYPE(*FILE) USER(QUSER)

/********************************************************************/

Adding Exit Poit Program

ADDEXITPGM EXITPNT(QIBM_QZDA_INIT) FORMAT(ZDAI0100) PGMNBR(1) PGM(ESSELWARE/ZDAINIT) TEXT('ODBC/JDBC connection log')

/********************************************************************/

El 13/07/18 a las 10:56, Denis Robitaille escribió:

Good Idea, I checked and there are no program registerd

Denis Robitaille
Chef de service TI – Solution d’entreprise
Infrastructure et opérations

CASCADES CENTRE DES TECHNOLOGIES
412 Marie Victorin
Kingsey falls(Québec) Canada J0A 1B0
Tél : 819 363 6100 Poste :52130
Cell : 819 352 9362

-----Message d'origine-----
De : MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> De la part de Gary Monnier
Envoyé : 13 juillet 2018 11:35
À : Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Objet : Re: ODBC problem (I think)

Do you have an exit program on the ODBC-related exit points?

ODBC Exit points:
QIBM_QZDA_INIT called at server initiationQIBM_QZDA_NDB1 called for native database requestsQIBM_QZDA_SQL1 called for SQL requestsQIBM_QZDA_SQL2 called for SQL requestsQIBM_QZDA_ROI1 called for retrieving object information requests and SQL catalog functions

Gary Monnier
*ATI Administrator*
*425.427.7959*

On Fri, Jul 13, 2018 at 8:24 AM, Denis Robitaille < denis_robitaille@xxxxxxxxxxxx> wrote:

Hello,

I checked that audit journal. I fount GR, JS and ZC transactions.
Nothing unsual in them

The QZDASOINIT joblog did not show anything special either.

And just to let you know, the user I used has *ALLOBJ *SECADM
*IOSYSCFG *JOBCTL *SAVSYS *SECADM *SERVICE *SPLCTL special authority.
So it should have access to everything.

-----Message d'origine-----
De : MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> De la part de Bryan
Dietz Envoyé : 13 juillet 2018 10:52 À : Midrange Systems Technical
Discussion <midrange-l@xxxxxxxxxxxx> Objet : Re: ODBC problem (I
think)

have you checked the audit journal to see if there is anything else
that the user might not be authorized to?

were you able to find the joblog of the QZDASOINIT job to see if
anything in there was interesting?

Bryan

Denis Robitaille wrote on 7/13/2018 9:37 AM:

Hello all,

I am faced with a situation that I can not understand.

We have an excel spreadsheet that do a qwery on an Iseries. With
some

users it works fine, with other we get the following error:

[IBM][ODBC System i Access Driver][DB2 for i5/OS]SQL0551 - Not
authorized to object QZDA07600X in QGPL, type *SQLPKG]

With my user, it works fine so I cloned it. With the clone, I still
get

the error. The clone as *ALLOBJ but still gets the error.

I even deleted the QZDA07600X SKLPKG since it would get recreated
by

the system if necessary. I still get the error even if the object do
not exist!!!!

I am out of idea.

Here is what I think are the key information:
We are at 7.2

Here is the connection string:
DRIVER={iSeries Access ODBC driver}; XDYNAMIC=0;
PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;
LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=QGPL CDTAWHSE;
SYSTEM=cas800re.cascades.com;

The query is a simple SELECT from a single file

Any help would be appreciated

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.
midrange.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=
pApUd0AUA6FmKRo01iR_VA&r=r7w16KGEqE6t2tVCvCsz4_ztz5b0wbCtdhwrG0PzYq8&m
=
IZErN92WNgnaOdLlLAkjFW4ZBEINyb5VpdtaTlqoR8s&s=gkFEoJguT-
bB8JJFwKGzLJbR4IGWhirw3P5jDpJ6_EY&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__
archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=pApUd0AUA6FmKRo01iR_VA&r=
r7w16KGEqE6t2tVCvCsz4_ztz5b0wbCtdhwrG0PzYq8&m=
IZErN92WNgnaOdLlLAkjFW4ZBEINyb5VpdtaTlqoR8s&s=
NiJhgcDah7TcUT673PWccsuJgs3Yufm0TXEgUhnxrdc&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_
2dEadiD&d=DwIGaQ&c=pApUd0AUA6FmKRo01iR_VA&r=r7w16KGEqE6t2tVCvCsz4_
ztz5b0wbCtdhwrG0PzYq8&m=IZErN92WNgnaOdLlLAkjFW4ZBEINyb5VpdtaTlqoR8s&s=
4qf3r42GFLES4N_d_cDtmy24BD75_Kdu7J0jfskBSeY&e=

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD