1

I am using NoSQL in DB2 10.5 FP4. I am creating and connecting to the database with the 'db2admin' administrator user ID on my Windows machine. I also used the 'db2 attach to DB2_01 as db2admin' command in the DB2 CLP to make sure that db2admin owns the database, and everything I do is as that ID.

When I start the command line, I get the expected results the first time:

IBM DB2 NoSQL JSON API 1.1.0.0 build 1.3.44
Licensed Materials - Property of IBM
(c) Copyright IBM Corp. 2013 All Rights Reserved.
nosql>Type your JSON query and hit <ENTER>
nosql>Type help() or help for usage information. All commands are case sensitive
Meta-data tables and functions for NoSQL JSON have not been created or have been created incorrectly.
 Please run 'enable(true)' to create them.
 You must have the correct admin privileges.
 Run 'enable(false)' to see the SQL that will be used.
nosql>nosql>enable(true)
nosql>Executing SQL...
Database artifacts created successfully.
nosql>use myw3

It looks like everything worked. However, once I exit and go back into the NoSQL CLI, I get the following error:

NoSQL JSON user-defined functions were not installed correctly or are not functioning correctly. Please check your DB2 installation.
nosql>

If I look at the db2diag log, I see the following:

2014-12-09-16.40.49.036000-300 I2028450H1253        LEVEL: Severe
PID     : 5820                 TID : 7936           PROC : db2syscs.exe
INSTANCE: DB2_01               NODE : 000           DB   : W3DB
APPHDL  : 0-126                APPID: 127.0.0.1.64277.141209214050
AUTHID  : DB2ADMIN             HOSTNAME: dlwester
EDUID   : 7936                 EDUNAME: db2agent (W3DB) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRoutineResolveAddress, probe:10
MESSAGE : ZRC=0xFFFFFE44=-444
          SQL0444N  Routine "" (specific name "") is implemented with code in 
          library or path "", function "" which cannot be accessed.  Reason 
          code: "".
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -444   sqlerrml: 67
 sqlerrmc: SYSTOOLS.JSON_BINARY SQL141209160848336 \db2json jsonBinaryOrNull 6
 sqlerrp : SQLERLIB
 sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000
           (4) 0x00000000      (5) 0x00000000      (6) 0x00000000
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)    
           (7)      (8)      (9)      (10)        (11)     
 sqlstate: 42724
DATA #2 : String, 16 bytes
jsonBinaryOrNull
DATA #3 : String, 7 bytes
db2json

If I look at the database, I see the JSON_BINARY UDF in the SYSTOOLS schema. I wouldn't worry so much about the warning message when starting the JSON CLI, but I'm also having problems with my application when reading the NoSQL DB. I get the same error as above, except with the SYSTOOLS.JSON_TABLE UDF.

Any ideas what might be going wrong here?


Exception from the application:

com.ibm.nosql.json.api.DBException: [nosql][1.3.44] Routine "SYSTOOLS.JSON_TABLE" (specific name "SQL141209160848438") is implemented with code in library or path "\db2json", function "jsonTable" which cannot be accessed.  Reason code: "6".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.17.31;
 Caused by: DB2 SQL Error: SQLCODE=-444, SQLSTATE=42724, SQLERRMC=SYSTOOLS.JSON_TABLE;SQL141209160848438;\db2json;jsonTable;6, DRIVER=4.17.31
    at com.ibm.nosql.json.api.DBData.update(DBData.java:199)
    at com.ibm.nosql.json.api.DBData.update(DBData.java:220)
    at com.ibm.nosql.json.api.DBCollection.remove(DBCollection.java:1372)
    at com.ibm.nosql.json.api.DBCollection.remove(DBCollection.java:1421)
    at com.ibm.myw3.services.scheduler.source.sourceadapter.SmrtPlntHandlerTest$1.call(SmrtPlntHandlerTest.java:52)
  • You probably need to grant EXECUTE permission on certain packages. You can use SYSCAT.ROUTINES and SYSCAT.PACKAGES to locate packages. Another option might be to grant BINDADD on database to the particular user ID you are looking for. – Chris Aldrich Dec 10 '14 at 18:34
  • I've used the 'db2admin' user to do everything in setting up and creating the database, and it's the user that is failing. I looked in SYSCAT.ROUTINES, and all the 'SYSTOOLS' functions are owned by 'db2admin'. There are no PKGSCHEMA rows for 'SYSTOOLS' in SYSCAT.PACKAGES. So I'm not sure if this has anything to do with permissions. – Westy Dec 10 '14 at 20:46
  • But you are on Windows..... Interestingly enough, you can be attached to the instance as the instance owner and it can create the database under the ID of the user currently logged into Windows. You may need to log in as that ID and grant BINDADD or DBADM and what not back to db2admin. db2admin may currently have authority due to SYSADM_GROUP (ie, DB2ADMNS group in Windows groups). – Chris Aldrich Dec 11 '14 at 13:47
  • When I try to do GRANTs to 'db2admin' from 'dlwester' (which is the ID I login to Windows from, and which has Administrator authority), I get error messages: SQL0552N "DLWESTER" does not have the privilege to perform operation "GRANT". So I really believe that db2admin owns the database and the objects within it. – Westy Dec 11 '14 at 19:48
  • You can tell who owns what by checking out the SYSCAT.xAUTH views where x is the item or object being considered. Example to query DB level, SYSCAT.DBAUTH, tables are SYSCAT.TABAUTH, etc. – Chris Aldrich Dec 11 '14 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.