loopback w/ table schemas, not identifying schema in options

Question

Nature of the issue

My db2 database makes wide use of table schemas for organization, so the table in question is LIVE.TBLADDRESS -

My model uses the "options" to specify the table schema

"options": {
    "idInjection": false,
    "db2": {
        "schema": "LIVE",
        "table": "TBLADDRESS"
    }
}

the model is in the model-config.json using

,"Tbladdress": {
    "dataSource": "x3",
    "public": true
}

I get an error when I try to use the explorer to do a simple 'get' or any other API call.

"statusCode": 500,
"name": "Error",
"message": "[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "DB2X.TBLADDRESS" is an undefined name. SQLSTATE=42704\r\n",

Expected behavior

Once I specified the schema - I'd expect the API to resolve correctly

Actual behavior

The default schema for db user is used at all times...regardless of specified schema in options.

Suggested resolution

Maybe I set it in the wrong place, I will continue to look for the information, It is possible I am missing something.

This is what I "see" using DB Viewer...so you have an idea what I'm referring to.

DEV - host:50000/DEV
-schemas
|-AAA
|-BBB
|-DB2X (this is the schema that the error is referring to...but NOT the one specified in the model)
|-DDD
|-LIVE (this is the correct schema)
|--Tables
|--|-TBLA
|--|-TBLADDRESS
|-ZZZ

If it helps - this happens with manually create models or models generated by discovery scripts.

These are my config files, and model

/common/models/Tbladdress.json

{
    "name": "Tbladdress",
    "options": {
        "idInjection": false,
        "db2": {
            "schema": "LIVE",
            "table": "TBLADDRESS"
        }
    },
    "properties": {
        ...
    }
}

/datasources.json

{
    "db": {
        "name": "db",
        "connector": "memory"
    },
    "x3": {
        "name": "x3",
        "connector": "db2",
        "username": "...",
        "password": "...",
        "database": "...",
        "hostname": "...",
        "port":     50000
    }
}

/model-config.json

{
    "_meta": {
    ...
    },
    "User": {
        "dataSource": "db"
    },
    "AccessToken": {
        "dataSource": "db",
        "public": false
    },
    "ACL": {
        "dataSource": "db",
        "public": false
    },
    "RoleMapping": {
        "dataSource": "db",
        "public": false,
        "options": {
            "strictObjectIDCoercion": true
        }
    },
    "Role": {
        "dataSource": "db",
        "public": false
    }

    ,"Tbladdress": {
        "dataSource": "x3",
        "public": true
    }

}

http://localhost:3000/explorer/#!/Tbladdress/Tbladdress_findById

{
    "error": {
        "statusCode": 500,
        "name": "Error",
        "message": "[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  \"DB2X.TBLADDRESS\" is an undefined name.  SQLSTATE=42704\r\n",
        "errors": [],
        "error": "[node-ibm_db] SQL_ERROR",
        "state": "42S02",
        "stack": "Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  \"DB2X.TBLADDRESS\" is an undefined name.  SQLSTATE=42704\r\n"
    }
}

...Headers...

{
    "date": "Sun, 18 Feb 2018 05:20:36 GMT",
    "x-content-type-options": "nosniff",
    "x-download-options": "noopen",
    "x-frame-options": "DENY",
    "content-type": "application/json; charset=utf-8",
    "transfer-encoding": "chunked",
    "connection": "keep-alive",
    "access-control-allow-credentials": "true",
    "vary": "Origin, Accept-Encoding",
    "x-xss-protection": "1; mode=block"
}

USING:

loopback-cli v3 to generate express app

loopback-connector-db2 to connect to DB2 v10

Node v8.9.2

Package.JSON dependencies looks like this (as mentioned it's a default install, with one model added - to see if I could get it to work)

"dependencies": {
    "compression": "^1.0.3",
    "cors": "^2.5.2",
    "helmet": "^1.3.0",
    "loopback": "^3.0.0",
    "loopback-boot": "^2.6.5",
    "loopback-component-explorer": "^5.0.0",
    "loopback-connector-db2": "^2.1.1",
    "serve-favicon": "^2.0.1",
    "strong-error-handler": "^2.0.0"

},

Yes - the DB2 connector worked fine when I specified the "LIVE" schema on data discovery - but it does NOT seem to be working when I use the API. I don't know if it's the connector or the loopback app.

Answer 1

For loopback-connector-db2, you must define SCHEMA in the datasources.json config file.

{
  "x3": {
    "name": "x3",
    "connector": "db2",
    "username": "...",
    "password": "...",
    "database": "...",
    "hostname": "...",
    "port":     50000
  },
  "x3Live": {
    "name": "x3Live",
    "connector": "db2",
    "schema": "LIVE",
    "username": "...",
    "password": "...",
    "database": "...",
    "hostname": "...",
    "port":     50000
  }
}

Unfortunately, you will need to create a new datasource (e.g. x3Live). Use the old x3 datasource for the models using the DB2X schema, and the new x3Live datasource for the models using the LIVE schema.