StackOverflow is a collaborative programming Q&A community venture
Note: The content of this blog is the opinion and thoughts of the blogger and does not necessarily represent the opinions of IDUG.
How do I convert date from BD2 format to string?
I want to convert
to 20180119 (YYYYMMDD) rather than with the hyphen 2018-01-19 (YYYY-MM-DD)
C# - Cannot implicitly convert type 'IBM.Data.DB2.iSeries.iDB2DataReader' to 'System.Data.SqlClient.SqlDataReader'
I am working in a Ranorex project, but it isn't a Ranorex specific problem. I need to open a connection to an AS400 DB2 Database and then run a SQL query against it.
I have code that successfully opens the connection and returns the results of the SQL query.
In the code, the second method, calls the first method to open the connection, which then allows the second method to run the SQL commands, but I get the following error:
at the 'return new iDB2Connection(cn.ToString())' point in the first method.
I can't workout how to resolve the issue and I have not been able to find any information on the error itself. I would be grateful for any support/advice people can supply.
How to find transactions with same transacrionTo
Let's say we have a table called Transaction. Each transaction has an id(key), a transactionFrom(number) and a transactionTo(number). How can I find all the transactions that have for the same transactionTo but different transactionFrom? Any ideas? Thanks!
Cannot use column names in SQL queries of DashDB/"Db2 Warehouse on Cloud"
I loaded some sample data into Db2 Warehouse on Cloud but I'm not able to execute even basic SQL queries since I'm always getting "QL0206N: SQL0206N "[column_name]" is not valid in the context where it is used. SQLSTATE=42703".
I tried multiple variants like
I also tried different columns and tried to put them into different places of the query (e.g. in "where" or "on" clauses) => the same result.
Surprisingly, I can query tables from sample schemas (e.g. "GOSALES") normally => so the problem occurs only for my schema (but for all tables).
Locate where is the nth occurrence of a token in a string separated by pipes
I'm I am a newbie with Regex and would like to know if it is possible to do that.
It is possible to locate the token position of a sub-string in a string like the below sample text?
Requesting the position of the 1st occurrence of 'XXXX' I must get '3', requesting the 2nd occurrence of 'XXXX' I must get '5', requesting the 3rd occurrence of 'XXXX' I must get '0' cause there's no a 3rd ocurrence.
This can be done using just regex?
Thanks in advance.
PS: If it is possible I will implement this solution on DB2 v7r2 using REGEX functions to replace an UDF I write long time ago on PLSQL to do this job.
DB2 backup with BLOB fields
Majority of the DB2 space is occupied by fields of the type BLOB and CLOB. Currently, full backup of the entire database takes very long time and goes beyond time limits. We tried to use incremental backup to speed up backup, but problem did not go away as BLOB and CLOB fields are still being pulled into the backup, irrespective of whether they were changed, so it is almost the same as doing a full backup.
We decided to proceed with following approach:Create a new tablespace and put there tables with BLOBs and CLOBs. Old data will be archived once a year, and this tablespace every day.Once the size of tablespace exceeds a certain limit, we will create a new space and start writing new data to it, and so on.
Problem occurs with restoration when using above mentioned approach.
Is this feasible approach for backup and are steps we take to backup and restore adequate?Is there a better way to speed up backup, while still maintaining same level of resilience?
Connect Python Flask web server application to DB2
I have a python flask Bluemix/IBM cloud-application for which I would like to use to move data from Cloud object storage to DB2 Warehouse on Cloud.
To make it easier I started by creating this code in a Data Science Experience Notebook, which works perfectly. To connect to DB2 I am using the IBMDBPY-package. Now I am starting to migrate my code from the Notebook to the python flask application.
The connection to Cloud object Storage works, but when I want to connect it to DB2 it fails due to
To be able to connect this locally I need to install a JDBC driver for DB2 which I can't because it is not supported on a Mac. According to the IBMDBPY-guide I have to upload two files from the JDBC driver to the directory where the IBMDBPY-package exists. But Because the packages are imported from the requirements.txt in the Flask-application I can't really put them in that folder.
Is there any solutions or workarounds regarding this? If I can only make it work when the application is running on the server and not locally that is fine as well. As long as everything works fine when application is running.
Process Each row in stored procedure or SQL in DB2
I am basically from Oracle background and trying to create a SQL code or stored procedure which will execute REORG for each table which is in reorg pending status
I have already figured out way to get list of such tables as given below
SELECT 'CALL SYSPROC.ADMIN_CMD(''REORG TABLE', substr(rtrim(TABSCHEMA)||'.'||rtrim(TABNAME),1,20) ||''')', ';' from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y' AND TABSCHEMA=CURRENT SCHEMA
Now my requirement is to run this result set as a DB2 SQL command for each row found, I don't have option to use shell script or batch file , it has to be done using SQL or stored procedure. I tried creating proc but lots of issues with that , can someone pl help
Golang db2cli running select query gets cgo error
I'm trying to use db2cli library (https://bitbucket.org/phiggins/db2cli/) to implement DB2 queries (select, insert, delete). But get CGO error when trying to run select queries. Here is my code:
The error is:
If I manually run the select queries from DB2 command line, it will return correct results. And other type of queries (insert, delete etc.) with Go language code also work correctly. I tried to set GODEBUG=cgocheck=0, but still get the same issue. Has anyone encountered similar problems when using db2cli?
How to create a user defined function that returns a table in a DB2 module?
I am trying to create a user-defined function that returns a table in DB2. Here is what I have so far.
This is a table that I use:
This is a user-defined function that returns a table (which is working fine):
This is a how far I was able to get with the module:
Any attempts to actually add a function to a module failed with various errors. Here is my DB2 version information: Database server = DB2/LINUXX8664 184.108.40.206This is an Express-C installation under Redhat.
When I try this, I get SQL0628N Multiple or conflicting keywords involving the "RETURNS" clause arepresent. LINE NUMBER=16. SQLSTATE=42613
When I try this (removed RETURNS clause), I get SQL0491N The CREATE FUNCTION or ALTER MODULE statement used to define"CORPDATA.MODULE1.DEPTEMPLOYEES" must have a RETURNS clause, and one of: theEXTERNAL clause (with other required keywords); an SQL function body; or theSOURCE clause. LINE NUMBER=8. SQLSTATE=42601
When I try this (removed BEGIN ATOMIC), I get SQL0104N An unexpected token "SELECT" was found following "INISTICRETURN ". Expected tokens may include: "(". LINE NUMBER=9. SQLSTATE=42601:) Yes, it does say "INISTIC".