Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Exclamation Unanswered: Specific Name Not Found

    I am encountering a very peculiar error.

    This is how I explained the issue to my "bosses":

    When running our tests, the HitList portion takes a dive; specifically CreateArchiveQuery. We get the following error:
    Message: [IBM][CLI Driver][DB2/6000] SQL0444N Routine "*iveQuery" (specific name "SQL040422094524940") is implemented with code in library or path "...eArchive
    Query", function "AIM.CreateArchiveQuery" which cannot be accessed. Reason code
    : "4". SQLSTATE=42724

    What is happening is this: in DB2 when you create a stored procedure, there are two names: a "logical" name and a specific name. The specific can either be generated by DB2 or explicitly stated. When we create our stored procedures, we let the system generate the specific name. This has never caused us problems.

    AIM for some reason is calling the stored procedure with the wrong specific name. Hence, the can't find code error message.

    I dropped and recreated the stored procedure hoping to "shake loose" whatever it was the application may have been holding on to. When the procedure was recompiled, DB2 gave it a new specific name. When we ran our test again, we got the same error. However, the application was calling the wrong specific name with a different specific name from the previous run (instead of specific name "SQL040422094524940", it was using specific name "SQL040422094526512").

    Our third attempt to rectify the problem was to give it an explicit name. The stored procedure was dropped, it was recreated with a specific name of CreateArchiveQuery, and ran fine from my machine (return values, etc.) When we tried our test, same problem: the specific name the app was using was different from before but was still wrong.

    Has anyone seen anything like this? I thought dropping and recreating the procedure in question would take care of it, but that doesn't seem to be working. Should I try dropping it a few more times or is there an easier way to solve this issue?

    Any help would be appreciated!!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    Hi,

    are you dropping the right procedure?
    One reason of giving the specific name to a procedure is overloading of the procedure, so use specific name in dropping the procedure and recreating it.

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    There is only one procedure with that name. When I drop it , it's gone and so are the packages associated with it.

    I have dropped other procedures before without using the specific name and haven't had any problems at all...

    If someone tried to migrate a stored procedure by moving binaries and messed something up, could there be remnants of that stored procedure hanging around somewhere? It seems odd this only happens with one stored procedure.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Mar 2004
    Posts
    448
    Every stored procedure created also created an executible file, it is located at
    <instance home>/sqllib/function/routine/sqlproc/<database name>/<owner name> ,here you will find the package file/executible file(pardon me if I am calling it package file,not the right word).
    if you run this query

    select procname,implementation from syscat.procedures where procname='<procname>' you will find the package file name.
    so if you drop that procedure, db2 automatically delete this file and other files related to that package *.sqc *.c.
    so when you migrate/backup/restore the database,we generally saved the files at these locations and manually move that files to our new location.
    A procedure that is overloaded(means same name but different specific names,also different no of parameters) must have different package files at this location.
    you can read some of the files at this location to better understand what was happening when you run the create procedure command, which c compiler it is using etc

    so check this location, check the catalog views procedures and packages in syscat schema,drop the procedure , also check the owner and the schema of the package

    Regards,

    Mujeeb

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Thanks for the clarification!!!

    But here's another question: when I invoke the stored procedure from command line, stored proc builder, etc., it runs just fine. But when we try to invoke it through our application (Tibco, ADB) via our application server, that's where it blows up.

    Would an application go looking in the wrong place?
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Does the db2diag.log or notify log give any info

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Mar 2004
    Posts
    448
    what type of error you are getting at your application level.
    what's you code,, just put few lines containing that call.


    regards,

    mujeeb

  8. #8
    Join Date
    May 2004
    Posts
    3
    Quote Originally Posted by ansonee
    I am encountering a very peculiar error.

    This is how I explained the issue to my "bosses":

    When running our tests, the HitList portion takes a dive; specifically CreateArchiveQuery. We get the following error:
    Message: [IBM][CLI Driver][DB2/6000] SQL0444N Routine "*iveQuery" (specific name "SQL040422094524940") is implemented with code in library or path "...eArchive
    Query", function "AIM.CreateArchiveQuery" which cannot be accessed. Reason code
    : "4". SQLSTATE=42724
    I've got the same error using ADO, although once it worked.

    Try looking if the interface used to connect to DB2 is ADO for your aplication.
    There might be some issues between ADO and DB2 Client.

  9. #9
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Thought we had the problem licked. I dropped all the stored procedures and recreated them all with explicit specific names.

    Everything seemed to be cool unitl we tried to execute one of them; came back with that error message.

    If we needed to manually shake this loose, would these be the proper steps to follow:

    - Find all *.c and *.sqc files associated with the package in question
    - Drop the stored procedure
    - If the files don't get deleted, delete them manually
    - Recreate the stored procedure with the explicit specific name

    If I've missed something, please feel free to point me in the right dirction.

    Apprceiate the help...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  10. #10
    Join Date
    Mar 2004
    Posts
    448
    you need that .sqc files. also check sample/c , here you will find the bldapp script, read it.



    regards,

    mujeeb

  11. #11
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    What exactly am I looking for in the bldap.bat file? I'm not a C developer - just a dba.

    We're runnign DB2 on AIX - if I follow those steps outlined above, will that be enough to clear up the problem. I've already dropped and recreated the stored proc in question three times - each time with an explicit specific name, but still keep getting the "name not found error".

    The dba assigned to my project isn't the sharpest tooth on the saw so will need to pretty much walk him through the process...

    Thanks again
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  12. #12
    Join Date
    Mar 2004
    Posts
    448
    Now these steps that I followed.

    1. Create/registered you procedure with the Create procedure program.you don't need *.sqc file at that time.
    2. Use prep command on the *.sqc file to create a package and the *.c file.
    The package is stored in the db2 with the same name as the *.sqc file name.

    3. use c compiler to compile your *.c file into *.o files.

    4. use c compiler (use the link options) again to link all the *.o files that you need. this will create an executible library.this library should be stored at specific place (i forget the place).

    5. when that procedure is called db2 calls the library automatically.it uses the current schema register(generally this is the authorization id of the user), this is little confusing but when you call the procedure , it automatically add this schema.
    6. grant execute on that procedures.

    if you still have any problem ,if you like send me the procedure and the *.sqc file.
    I will check that ,my email is bmujeeb@hotmail.com

    regards,

    mujeeb

  13. #13
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Thanks for the reply but the process you outlined is just too complicated for the dba on our project - he knows nothing about compiling, C, etc.

    Can I just drop the stored procedure, delete any associated files for that procedure (*.sqc, *.o, *.c, whatever), and then just build the procedure again?
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  14. #14
    Join Date
    Mar 2004
    Posts
    448
    You need *.sqc file.you can delete the other files.The whole procedure that posted is the building of the "C" stored procedure.


    regards,

    mujeeb

Posting Permissions

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