RDB V.8.0.1.1
DB2 V9.7
Windows XP
Rich UI test project

First time using DB2 on Windows. I have done the following:

1. Created schema PINSTRUC
2. Create table PROCESSORMASTER
3. Granted all authority on schema to user NATHANREED
4. Granted all privileges on table to user NATHANREED
5. Established DB connection. Can successfully ping DB. Screen shot of build descriptor attached.
6. Created a record part.

package records; // basic record//record ProcessorMaster type SQLRecord                        end

When I attempt to “Retrieve SQL within the above part I get the following error:

IWN.VAL.4582.e 0/0 Retrieve SQL failed because table
Nathanreed.ProcessorMaster is not defined in database or because the user ID lacks SELECT authority.

I am not sure if I have a security/authority issue or a configuration issue within EGL.
Not yet familiar with dot syntax referring to Nathanreed.ProcessorMaster.

Any suggestions would be appreciated.

Thanks,

Nathan

Turned on trace for the connection and attached first trace file.

Attached second trace file.

Nathan,

Normally, you would want to use the "tablenames" property on the sqlRecord.

When you do this, if you don't specify the schema name as part of the tablename, then the way DB2 works (not EGL) is that it will use the logged userid as the schema name.

So, you may just want to create the tablename qualified with the Schema name.

Mark,

Am on track now. Thanks much for the explanation.

Nathan

Hi Mark,

I'm having same problem to try retrieve sql record definition.

But the customer DB2 Server is on zVSE or zVM ?

I could select the table and return row, but cannot retrieve.

record AEAJ type SQLRecord  
    {tableNames = [[TEST.AEAJ", "L1"]]}

end

IWN.VAL.4582.e 0/0 Retrieve SQL failed because table TEST.AEAJ is not defined in database  or because the user ID lacks SELECT authority.

 

SELECT * FROM TEST.AEAJ

Query execution time => 47 ms

Is there some special configuration to EGL connect to DB2 zVSE or zVM ?

Today, the customer works fine with VisuaAge SQL Retrieve.

thanks !

Hsieh

 

Hsieh,

I have seen this message come out many times when the JDBC packages are not bound to the host database.      VAGen does not use JDBC so this is not required from VAGen.

 

You normally have to run the DB2 binder to bind these packages.    The exact methodology depends on the the database and what setup you have.  By that I mean if you have the right .bnd files on the workstation, you can do it via the workstation.  If you don't have those, you may need to get the DBA/sysadmin to run them on the host DB.   They would have to look at the setup info for JDBC access.

 

Finally, one way to verify what is going on is to run a DB2 trace.  This is done via the properties for the connection.  There is a trace tab and you can enable all tracing, do the retrieve, and then see the results.  I am guessing you will have some error trying to access the SYSIBM tables (or whatever the name is on z/VSE or z/VM.

 

Mark

 

 

Hi Mark,

After the trace on, I checked the error sqlcode -332 is character conversion problem.

We made again bind @ ddcsvm.lst with CCSIDS parameter 1208 but did not fix the error.

We try to CCSIDS 1252 and also has not fix.

The DB2 zVM CCSIDS-SBCS=500.

We're out of ideas !

 

Hsieh

Hsieh,

If you are still getting some form of SQLCODE then the retrieve is not going to work.    What SQLcode are you getting now?   Are you still getting a sqlcode -332?   Are you sure the "rebind" took affect?

If you attach the trace, I might be able to spot something, but you could also try and find a DB2 on z/VM forum to ask.

take care.

Mark

Hsieh,

 

You could also open a PMR with the DB2 folks.

 

take care. 

 

Mark

Thanks ! Mark.

Attach the trace files.

The client is very complicated, they have not IBM Support for DB2 zVM and We cannot open PMR.

I will take a look... What is the exact db and version they are connecting to?

 

DB2 v.7.4

I took a quick look and don't know what is causing it.

It does seem to think it is trying to convert between the code pages of 0 and 500 and it does not like it.

 

A couple of things to try.

- what driver properties are you using.    Are you using a Type 2 driver as recommended in the EGL for VSE Feature runtime guide.  See the attached guide.   I think you are using the type 4 (universal data driver) based on the trace output.

- I would suggest adding the following property to the URL string used to connect with    emulateParameterMetaDataForZCalls=true.  Sometimes this gives better info or handles the remote communications better (at least it does with z/OS).

 

Hi Mark,

I had read this document before the question and I followed the instructions but occurred an another problem. See the steps I did in the attached document.

I also believe that we should use the JDBC type 2, but can not stabilize the connection problem in RBD.

No idea !

Thank you for monitoring.

Hsieh

Hei Mark,

I finally got to connect with DB2 zVSE using the driver db2java.zip of the DB2 Connect version v.8.2. The DB2 Connect v10.5 did not connect.

Now I try to do SQL Retrieve but nothing happened, no SQL RECORD retrieve.  No SQL ERROR and no error message.

Then I tried to start DB2 Connection with TRACE ON and I get a message

I have add license v.8.2 and v.10.5

db2jcc_license_cisuz.jar

db2jcc_license_cu.jar

the same erro occurs.

Do you have any idea ?

 

I think those license files are only valid for the db2jcc.jar jdbc drivers...but not sure.  

is DB2 Connect for the level zip file you are using installed on this machine.   You might not need to run it, but I think you need to install it.   We shipped a copy of db2 connect 10.1 with the optional images shipped with RBD 9.0, so you could download from there if you don't have a copy.

 

Where did you get the copy of db2 connect 10.5?   Or maybe the license was not installed correctly.

 

From Google, there is a command named db2licm which can be used to get info about and install a license.

 

Mark

 

Mark,

The customer has the licence softwares in midia.

>db2licm -l show detail

Product name:                     "IBM Data Server Client"
Product identifier:               "db2client"
Version information:              "10.5"

 

One more thing:  

We have installed "IBM Connect V.10.5" but using the db2client.zip v.8.2 to RBD connect to DB2 zVSE.

I can access DB2 tables and EGL Debug works fine.

But I cannot to do SQL Retrieve because  nothing happened, no SQL RECORD retrieve,  no SQL ERROR and no error message.

(Attached the db2trace out)

Please, you can send a copy db2client.zip v.10.1

Thanks !

Hsieh,

Do they have DB2 connect installed?   This is what I think is required.   I do not have a copy I can send you.  It has to be downloaded from passport advantage as I described before.

BTW... I can see from the trace that the connect happened and some queries ran.    Was anything tried other than SQL retrieve?     But I can't see where we are getting column data for the table.

And... have you tried the Data Explorer (in the data perspective (or database developer)).  Does it work?

I have asked a couple of others...so will respond when I hear back.

 

take care.

Mark

 

 

 

 

Hi Mark,

I'm sure ! The DB2 connect is installed.  

DB2 Connect Enterprise Edition v.10.5

When I used db2client.zip v.10.5 I could not in the data perspective and data explorer retrieve the schema, table, columns, ... not work !

But using db2client.zip v.8 I could in the data perspective and data explorer retrieve the schema, table, columns, ... works OK !

and only SQL Retrieve do not work.

 

Now I'm doing EGL Debug for some program access remote DB2 zVSE using db2client.zip v.8 ... works OK !

However, I found another problem.

 

The SQLcondition:

WHERE FT30GRUP =  :FT05GRUP 

The columns FT30GRUP  in the DB2 table is defined char(08) and the host variable FT05GRUP is defined char(10)

I got  the SQLCODE -302:

THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE

 

and the program generated to COBOL CICS/VSE works fine !  no SQLCODE error.

The question are:

            Who makes the handling of SQL statements ?

EGL is that takes the SQL statement, assembles and requests to DB2 for execution?

RBD works with DB2 zVSE 7.4 ?  Which DB2 Connect version driver is supported  ?

I do not know who is in trouble? RBD or DB2

We have opened two PMR .. RBD and DB2 Connect

 

Thanks for the input on what did/did not work.   I am getting input from my team.

 

I am surprised that the data explorer will work and SQL retrieve will not.  These use a lot common info.

 

One trick I found in a document when i was searching that might/might not work in this case.   Try unchecking the  "Retrieve primary key information from the system catalog" option in the EGL->SQL preferences.

 

On the -302, I don't think much can be done with this.  I believe this is a case of one way of accessing the data (COBOL and static)  is being more forgiving that than the other (through the JDBC driver and dynamic).   We just report the sqlcode we get back from DB2.   I don't think it is a defect.  

 

I did find one reference that during the prep of dynamic statements, the datatype of the column on the left of the where expression is set to the datatype of the right hand side (the host variable).  In COBOL, this is probably resolved as the precompile has access to both pieces of info.  One suggestion it made was to do something like WHERE CAST(FT30GRUP AS CHAR(8)) = :FT305GRUP.    (or you may need to cast as CHAR(10). 

 

Mark

The fix for the reference

 

 

Mark,

Thanks for monitoring this post.

I changed "Retrieve primary key information from the system catalog" option but not worked too. No SQL Retrieve.

 

Uoooll .. but all programs were migrated from VAG to EGL, I don't know how many case I have change to do CAST.

But this problem SQLCODE -302 is only to DB2 zVSE or to  DB2 zOS too ?

Please, could you make EGL Debug this one in DB2 zOS ?

and I will try make VAG ITF if it works ?

 

thanks

I tried this when debugging against DB2 on z/OS and it worked.  So maybe it is something specific to using the app driver or db2 on vm/vse.   Either way, EGL does not change the way it passes the statements as EGL does not really know what the target database is...except that it is "db2" and via a connection URL.   You could try the same thing from the data explorer with a where col = "1234567890"  (i.e. ten characters) and see what happens.

 

// basic program
//
program getjob type BasicProgram
{ includeReferencedFunctions = yes, allowUnqualifiedItemReferences = yes,
  throwNrfEofExceptions = yes, handleHardIOErrors = no, V60ExceptionCompatibility = yes,
  I4GLItemsNullable = no, textLiteralDefaultIsString = no, localSQLScope = yes}
 
    
    employee employee;
    hostjob char(10);
    function main()
        hostjob = "PRES";
        try
            get employee with
                #sql{
                    select
                        EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO,
                        HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS,
                        COMM
                    from MEVANS.EMPLOYEE
                    where
                        JOB = :HOSTJOB
                };
             writestdout("employee = " + firstnme + " " + lastname + " " + job);
            onException
                writestdout("error - SQLCODE = " + sysvar.sqlData.sqlcode);
            end
            
        
    end
    
end

record employee type sqlRecord
  {tableNames = [["MEVANS.EMPLOYEE"]],
  keyItems=[EMPNO], fieldsMatchColumns = yes}

    10 EMPNO char(6)          {column="EMPNO"};
    10 FIRSTNME char(12)      {column="FIRSTNME", sqlVariableLen=yes};
    10 MIDINIT char(1)        {column="MIDINIT"};
    10 LASTNAME char(15)      {column="LASTNAME", sqlVariableLen=yes};
    10 WORKDEPT char(3)       {column="WORKDEPT", isSqlNullable=yes};
    10 PHONENO char(4)        {column="PHONENO", isSqlNullable=yes};
    10 HIREDATE char(10)      {column="HIREDATE", isSqlNullable=yes};
    10 JOB char(8)            {column="JOB", isSqlNullable=yes};
    10 EDLEVEL smallInt       {column="EDLEVEL", isSqlNullable=yes};
    10 SEX char(1)            {column="SEX", isSqlNullable=yes};
    10 BIRTHDATE char(10)     {column="BIRTHDATE", isSqlNullable=yes};
    10 SALARY decimal(9,2)    {column="SALARY", isSqlNullable=yes};
    10 BONUS decimal(9,2)     {column="BONUS", isSqlNullable=yes};
    10 COMM decimal(9,2)      {column="COMM", isSqlNullable=yes};

 
 end

 

Also, I know this is a VAGen to EGL migration.   There are some environmental/coding items that cannot necessarily be overcome.   More research is needed on if something else is going on here, but you have to hope that it is not normal to have the host variable be a different length than the column definition.

 

 

Mark,

We running the same program in VAG ITF and It works fine. With DB2 Connector version 7.2.

also I agree that byte length between table column and host variable is incompatible.  

But why a new version 8.x or 10.x JDBC began to handle this?

Mark,

I made a sql script select with where col = '1234567890' and executed OK !

Do you know if VAG or EGL using the package bind DDCSVM.LST that comes with the db2 Connector ?

The only thing different version may be in the DDCS packages.

New update:

It works Ok !

try             open FT001F-SI-FT001RS1_RSI01                     with #sql{         select T1.FT30SIGL,T1.FT30PROG,T2.FT31DESC         from T02FT.FT30 T1,              T02FT.FT31 T2         WHERE FT30USER = 'PSC6' AND FT30GRUP = '1234567890'  AND FT30SIGL = FT31SIGL         ORDER BY FT30SIGL       }                     into FT30SIGL, FT30PROG, FT31DESC for FT001RS1;         onException(sqlexcp TypeCastException)

 

It didn't work.

FT05GRUP char(10) = "1234567890";         try             open FT001F-SI-FT001RS1_RSI01                     with #sql{         select T1.FT30SIGL,T1.FT30PROG,T2.FT31DESC         from T02FT.FT30 T1,              T02FT.FT31 T2         WHERE FT30USER = 'PSC6' AND FT30GRUP = :FT05GRUP  AND FT30SIGL = FT31SIGL         ORDER BY FT30SIGL       }                     into FT30SIGL, FT30PROG, FT31DESC for FT001RS1;         onException(sqlexcp TypeCastException)             ;         end

Hsieh,

Ok... thanks for the testing.

I understand the difference now between database explorer and EGL.   EGL does a prepare/execute on the statement.   The Database explorer just runs the statement somehow without doing a prepare.     The prepare uses something called parameter markers and then at execute time the values are filled in for host variables.    So, there is no checking done at prepare time and then when the data type does not match at execute time, then you get the -302.

After thinking about it more overnight, I tried again in z/OS with a host variable containing 10 non-blank characters.   In z/OS it gave a -302 as well.  If there are trailing blanks...DB2 takes care of it.

 

On your question re: DDCSVM.lst.... my point from before is that EGL does not "use" any of these or even know that was the bind being used.  We make a request to the JDBC driver and it is up to the JDBC driver to communicate to the database server using whatever bind was done for it.   In other words, we have no clue whether the target server is z/OS, VSE, i, windows, etc.   We just connect using the provided URL and do the same JDBC calls.   So, the DDCSVM.lst (or any other bind list) is just part of the setup required by DB2 to allow a distributed client access to the host server.

 

If you are wondering why VAGen would work in ITF, as I remember it, VAGen was "bound" to the database because we used a C interface and not JDBC.    JDBC is why we have to use prepare/execute.

 

This is just going to be a difference between VAGen and EGL (or DB2 through C vs DB2 through JDBC)....  It is not a defect and the level of the driver will not make a difference.

 

Mark

 

 

Thanks !  Mark.

Yes, I remember that VAG need to do bind package to run ITF.

 

Ok !  Now I understood.

but it will be a long conversation with the customer to accept this problem when you use the EGL Debug. :-) :-(

they will have to fix on average they find the error in EGL.

 

and about SQL Retrieve ?  (laughs)

 

Hsieh

Hsieh,

On the conversation with the customer...it should only affect them IF:

- They have a host variable whose data type does not match the column.  If they are using the SQL record fields for the host variables then that is much less likely..  So, just depends on how they "normally" coded.

- They do not have trailing blanks in character fields which would make the value length match the column.

 

On the SQL retrieve... I am still waiting on feedback from my team.  Hopefully soon.

I know it has worked in the past, but normally if SQL Retrieve fails then also the Database explorer does also.

 

One question...are the "tables" actually views or are they the actual table?

 

take care.

Mark
 

Mark,

Ok !  I will also analyze how are the legacy vagen code.

They do not using VIEW and SYNONYMS (alias).

I do not know if you noticed, in the last one trace attached, it tries to get the SQL RETRIEVE information on DB2 table SYSTEM.SYSSYNONYMS.

search this:

SELECT CREATOR,TNAME FROM SYSTEM.SYSSYNONYMS WHERE USERID='TEST' AND ALTNAME='AEAJ'

The correct is:

SELECT CREATOR,TNAME FROM SYSTEM.SYSCATALOG WHERE USERID='TEST' AND ALTNAME='AEAJ'

do not you think ?

thanks !

Hsieh

Good question.   will have to let the team look at it and decide if that might be the issue or something else.

 

Hey,

Just wanted to give you an update.

 

There are indications that there might be a change to the underlying data tooling we inherit that is keeping the VSE SQL Retrieve from working.   We are looking into it.

 

Mark