Issue while connecting to IBM DB2 server, using loopback4 framework and loopback-connector-db2

[Shankar-CodeJunkie]

My dependencies

"dependencies": {
    "@loopback/boot": "^7.0.3",
    "@loopback/core": "^6.1.0",
    "@loopback/repository": "^7.0.3",
    "@loopback/rest": "^14.0.3",
    "@loopback/rest-explorer": "^7.0.3",
    "@loopback/service-proxy": "^7.0.3",
    "loopback-connector-db2": "^2.2.3",
    "tslib": "^2.0.0"
  },

Steps to reproduce

lb4 app lb4 datasource <>. ---- Choose IBM Db2 (for Linux, Unix, Windows) (supported by StrongLoop) and also use dsn instead of individual values lb4 repository lb4 controller

Current Behavior

When you run npm start, the application starts, when you hit any of the endpoint, where you have the logic to have database interactions, it errors out even on create / count methods. Any interaction is failing.

await this.wiusrappPersonV1Repository.create({"PROFILE_ID": "abc123"})

I see an error on my console saying

Request GET /manualsync/112 failed with status code 500. Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  "username.WiusrappPersonV1" is an undefined name.  SQLSTATE=42704

await this.wiusrappPersonV1Repository.create({"PROFILE_ID": "abc123"}) .then(x => console.log('hey', x)) .catch(err => console.log('err', err))

The above produce the below error message 

```[Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  "KIPRAKA1_IN_IBM_COM.WiusrappPersonV1" is an undefined name.  SQLSTATE=42704
] {
  error: '[ibm_db] SQL_ERROR',
  sqlcode: -204,
  state: '42S02'
}

However, I can confirm that the db2 system has the table TESTSHANKAR, which is configured or mapped on the model

import {Entity, model, property} from '@loopback/repository';

@model({
  settings: {strict: true},
  db2: {schema: 'WIUSRAPP', table: "TESTSHANKAR"}
})
export class WiusrappPersonV1 extends Entity {

  @property({
    type: 'string',
    id: true,
    generated: false,
    required: true,
    db2: {
      columnName: 'PROFILE_ID'
    }
  })
  PROFILE_ID: string;

  // Define well-known properties here

  // Indexer property to allow additional data
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  [prop: string]: any;

  constructor(data?: Partial<WiusrappPersonV1>) {
    super(data);
  }
}

export interface WiusrappPersonV1Relations {
  // describe navigational properties here
}

export type WiusrappPersonV1WithRelations = WiusrappPersonV1 & WiusrappPersonV1Relations;

Expected Behavior

The DB2 connection isn't getting established.

Additional information

I did check some existing issues for the error message, and followed the solution but not working

https://github.com/loopbackio/loopback-next/issues/2504

Related Issues

I did find this issue matching to my problem, and I used that solution but still getting the same problem

https://github.com/loopbackio/loopback-next/issues/2504

[dhmlau]

I'm not a DB2 expert but judging by the error message, it seems like you don't have the table name "username.WiusrappPersonV1". Perhaps the schema name of that table is WIUSRAPP? You might want to specify it in the datasource.

[Shankar-CodeJunkie]

Thanks @dhmlau

As mentioned on the issue #2504, I use dsn property to provide the DB2 server details, where I do mention the schema name ..

Yes correct the schema name of the table is WIUSRAPP. I have it specified on the datasource

My datasource is as below

import {inject, lifeCycleObserver, LifeCycleObserver} from '@loopback/core';
import {juggler} from '@loopback/repository';

const config = {
  name: 'IbmBludbDataSourcev1',
  connector: 'db2',
  schema: 'WIUSRAPP',
  dsn: 'DATABASE=Bludb;HOSTNAME=xxxx;PORT=50001;PROTOCOL=TCPIP;UID=xxx;PWD=xxx;Security=SSL;schema:WIUSRAPP'
};

// Observe application's life cycle to disconnect the datasource when
// application is stopped. This allows the application to be shut down
// gracefully. The `stop()` method is inherited from `juggler.DataSource`.
// Learn more at https://loopback.io/doc/en/lb4/Life-cycle.html
@lifeCycleObserver('datasource')
export class IbmBludbDataSourcev1DataSource extends juggler.DataSource
  implements LifeCycleObserver {
  static dataSourceName = 'IbmBludbDataSourcev1';
  static readonly defaultConfig = config;

  constructor(
    @inject('datasources.config.IbmBludbDataSourcev1', {optional: true})
    dsConfig: object = config,
  ) {
    super(dsConfig);
  }
}

[dhmlau]

In the dsn string, would it be schema=WIUSRAPP (consistent with the rest of the string) instead of using :?

[Shankar-CodeJunkie]

@dhmlau : Yes, that was a typo, but even changing it to schema=WIUSRAPP gives the same error message. Now I see the tablename appearing on the error message.

[Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  "KIPRAKA1_IN_IBM_COM.TESTSHANKAR" is an undefined name.  SQLSTATE=42704
] {
  error: '[ibm_db] SQL_ERROR',
  sqlcode: -204,
  state: '42S02'
}

I have also shown a screenshot of DBeaver image, where you can see the table exist on the database

Why UID name is getting appended on the query instead of schema name ?

Is this something similar to the issue documented on #145

[Shankar-CodeJunkie]

@dhmlau:

After researching, I found out that I was referring the schema name incorrectly on the dsn string. It seems, in the dsn string on the datasource.ts, we need to mention the schema name against CurrentSchema instead of Schema

The correct property name in the DSN string is CurrentSchema and I was wrongly referencing it as schema earlier, because of which it was connecting to an incorrect schema and unable to get to the table.

dsn:'DATABASE=dbname;HOSTNAME=hostname;PORT=50001;PROTOCOL=TCPIP;UID=username;PWD=pwd;Security=SSL;CurrentSchema=schemaname'

It would be better, if we have this updated on the documentation. I will open up a new Pull request for the documentation update. Thank you

You can close the issue. Thanks for your help