Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi.  I need some help formatting an OS400 DB2 SQL update query joining 2 files.  I'm trying to update an attribute in Table A based on some criteria in both Table A and Table B.  I've tried a bunch of different ways and can't seem to quite get it right.  Here's where I left off...

update tablea/itmpln a set a.nods = 5
  where exists (select * from tableb/itembl b where b.ltcod = 'M' and a.itnb not like 'M%' and a.whid = '1' and b.house = '1' and a.nods <> 0)

This did run but updated about 600 extra records it should not have.  The extra records come from the query ignoring the filter over tableb  ( b.ltcod = 'M' )

I'm thinking I'm missing an intial "where" subquery to create the join but am new to sub-query so not sure how to structure the statement.   thanks
0
Get free NFR key for Veeam Availability Suite 9.5
LVL 1
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

I need to know if an IBM(I)  command prompt override program was called, or was the CPP called directly on a command line.  

I need to identify if a command was called after using a command prompt override program was called after pressing F4, or was it called directly by typing all info in on a command line and pressing enter.

I tried sending a *EXT message to the CPP - that worked in all cases but ONE ---  If you type a command, press F4 the message gets sent, the CMD processing program ends, and the CMD UI panels show the command --- however if from here you hit F12 -- the *EXT Message still remains there, then executing the CMD on the CMD line without prompting, the CPP will find the *EXT message from the previous F4, and then think the F4 prompting must of happened -- but it didn't

Any Ideas ?
0
I am trying to export the data of a table on AS400 to another machine through iSeries commands but i am stacked in the middle of the process. I have a stored procedure in which i create the CSV file but after completion i need to transfer this file to another machine (which is of course connected to the AS400).

In the stored procedure, i used the CPYTOIMPF command to export table data to CSV and i wrote the file on the AS400 file system. I don't know if there is an option to write the file directly to another machine.

CALL QSYS2.QCMDEXC('CPYTOIMPF FROMFILE(LIBRARY/TABLE) TOSTMF('/QIBM/UserData/TestFolder/2.CSV') STMFCODPAG(*PCASCII) RCDDLM(*CRLF)'); This step is completed and the file is written on that directory.

Now i need to transfer this file to a web server that is connected to AS400 without after the above command is completed.
Any suggestions?
0
Hi Experts ,
I'm using MS Exchange 2013 STD in DAG.
 
I have been trying to move all the mailboxes from database db1 to db2 i have moved all the mailbox successfully except one particular mailbox . When i start the migration job it starts and says syncing ,but it syncs forever doesn't show any data transferring status.it synced status is always zero.

Please find the attached image for reference . In the screenshot "ajt" is the email address which i'm facing issue to move to different database.

Please help me.
screenshot.png
0
Hi Experts,

I have to alter a serie of programs, that using calls to:
CMALLC, CMINIT, CMRCV, CMSCST, CMSEND, CMSPTR, CMSST and CMSTPN.

I found these objects in QSYS2 so I assume these are IBM supplied programs and now understand that this is SNA related.
Because of an outdated OS I cant install our standard communication tools, so I decided to use a Data Queue to exchange information with 1 other system.

I'm not familiar with this Cxxxxx programs and not sure if I need all of them for the DTAQ solution,
if I look to the source I have to alter, I assume it must be enough to use the CMSEND and CMRCV part?

I already looked to the IBM info, but that's not a big help :-(

Is there more info, or can someone confirm my assumption

Thanks,
0
Hi Experts ,

Getting error mid of restoring . 70% process is done but after that getting below error .  DB 10.5.8

SQL0902C  A system error occurred. Subsequent SQL statements cannot
          be processed. IBM software support reason code: "2"

Please suggest .
Regards,
Dharmesh
0
Dear
There is any way to find a reason if insert record into database file not successfully inserted in journal

Thanks
0
ODBC setup successful; however, connecting from SSIS using ODBC trigger the following error message.


Error [42968][IBM][CLI Driver] SQL1598N. An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968.
0
I am trying to write a function in DB2 and the function will parse a certain string based on delimiter and return as seperate fields .
The string I should pass will come from another query ,

example :
CREATE FUNCTIONSPLIT_STRING (varchar string)
  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
 
   RETURN values (tempstring,tempstring1);--
END;

Now can I call this function and pass a set of strings like select somestring from table ? or use the same query select somestring from table in the function and iterate through it ?
0
Hi All,

We are doing a small POC to migrate MF screens to JAVA based webpages. As a matter of fact, we have created COBOL SPs to call relevant MF DC online programs. In case we need to call COBOL SP, what approach do JAVA team has to follow.

NOTE : IBM MQ is not supported by customer
              IMS DC (IMS is the online component of my customer. IMS and DB2 databases are used by administration system) is not suggested as it needs additional work
              Webservice / business services help should not be used.

What could be the approach do we have to follow?

P.s : I am neither JAVA expert nor Mainframe expert. Since I am managing a project, trying to get suggestions from various areas.

Moreover I heard something like wrapping COBOL stored procedure with DB2 so that it can be converted to DB2 SP(Do we have any approaches like that). In my customer's IT landscape they can call DB2 SP by using ODBC driver.

I am looking forward to see responses from experts.

Regards,
Krishna
0
Free Tool: ZipGrep
LVL 10
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

We have problem with the application run DB2ResultSet.Read(). sometime will get EROR [24000] [IBM] CLI0115E  Invalid cursor state. SQLSTATE=24000.

Database : DB2 for Linux, UNIX and Windows V10.5
Client: Windows 7 64bit

Method:
public int EventGetEvSegmentCnt(string SegmentID, string strEvntGroup)
        {
            int strGroupCnt = 0;
            string strSQL = string.Empty;
            DB2ResultSet objRs;
            if (string.IsNullOrEmpty(SegmentID) || string.IsNullOrEmpty(strEvntGroup))
            {
                strGroupCnt = 0;
            }
            else
            {
                strSQL = " SELECT COUNT(EVNT_CODE) AS EVNT_GROUP_COUNT FROM E_SEGMENT_EVENT WHERE C_SEGMENT_ID = " + SegmentID + " AND EVNT_GROUP = " + strEvntGroup;
                Common.DatabaseHelper helper = new Common.DatabaseHelper();
                objRs = helper.ExecuteResultSet(strSQL);
                if (objRs.Read())
                {
                    strGroupCnt = 0;
                }
                else
                {
                    strGroupCnt = int.Parse(objRs["EVNT_GROUP_COUNT"].ToString());
                }
            }
            return strGroupCnt;

        }

Error Message:
[Information]      System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> IBM.Data.DB2.DB2Exception (0x80004005): ERROR [24000] [IBM] CLI0115E  Invalid cursor state. SQLSTATE=24000
   at …
0
"OF DEL MODIFIED BY COLDEL,REPLACE" works fine and was able to load the records into the database
modified the same to
"OF DEL MODIFIED BY COLDEL'0x7c'REPLACE" as my original file is pipe delimited, now I'm getting below error for the same input file which worked for the "," delimited ( just did replace all for "," with "|") and there are no other ,

SQL3125W  The character data in row "F0-1" and column "1" was truncated
because the data is longer than the target database column.
0
I had this question after viewing Number of Weeks in a particular month.

This works great for Saturday but I my week starts on Monday (day 1).  How do I change this so that I can pick a different day, example, Monday (first day) or Sunday (last day) of the week.
Thank you!
0
We have prod environment and development environment  for DB2 .

1> we are thinking of proposing have a nick schema in dev pointing to prod .
in this case we will need to make sure only specific user id will have access to these nick schema .
is it possible to do that ?

2> if we fire select on  nick schemas< with ur > ; Is it sure that we will not lock  not lock the database tables .

3> Is there any way of controlling database load on the prod databases when queries are fired by nick schema ?
0
Hello. I imported a txt file as a table into QMF and a couple of my columns are displaying as 4.43300000000000E+001, etc.. How can I convert/format these to standard numbers in my query?

Thanks in advance,

James
0
Hello,

We have an index corruption Issue at (HADR environment ) founded into db2diag of primary caused crash , this corruption occurred on primary. I've decided to

1-takeover on standby as no page corruption there and deactivated primary to avoid this corruption moved to standby
2-backup (new primary ) and drop db on old primary
3-restore on old primary (standby) , then working successfully

but doesn't know what the root cause of this page corruption I faced this issue twice during 40 days. My suspect is maybe index not building at true bases, even db2support hadn't got particularity more details they said maybe application code

Is there any way to ensure an index is working fine or not?
As my understanding, there are many flavors of index-related corruption problems, for instance:

Unique index contains duplicates with different RID(s) or same RID(s)
Multiple index entries pointing to the same RID
Index key is out of place (wrong index key order)
Row exists, but index keys do not exist in any or some of the indexes
Index entry pointing to an empty data slot or unused data slot or RID is invalid
Incorrect previous or next index page pointers, incorrect high key or other corruptions on index pages

And if this happens again, is there any different method unless backup and restore?

I found This maybe can be a solution could you  please give me your thoughts  can i do this as a solution ???

REORG the corrupted indexes or mark the index as bad by …
0
I need suggestion around tools that can be used to convert sql, procedures from Oracle DB to IBM DB2 database.
Thanks in advance
0
I have followed instruction for creating /etc/init/db2fmcd. It's still won't restart.

I have tried the following link

http://www-01.ibm.com/support/docview.wss?uid=swg21209001

I still get the following

Gcf module 'fault monitor' is INSTALLED PROPERLY but NOT ALIVE
Gcf module '/opt/ibm/db2/V10.5/lib32/libdb2gcf.so' is INSTALLED PROPERLY but NOT ALIVE


If I run /opt/ibm/db2/V10.5/bin/db2fmcd by itself it just hangs.
0
I want to do something that seems like it should be very simple... I have single db2 instance, single db on it, I need a single user's credentials that will work to login via jdbc and have full rights to the DB (i.e. create/drop schema, etc)   But I can't get it to work.

I have a db2 10.5 instance installed on a RHEL 7 VM - I have created a DB, lets call it db123

I can access the DB fine if I am logged into VM on command line.  The username is dbinst1.  

I just want to be able to connect to this via squirrel (or other jdbc based client).  But I am getting authentication failure.  As far as I understand db2 uses OS authentication.   I am unable to log in as user db2inst1 it says

com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][2013][11249][3.68.61] Connection authorization failure occurred.  Reason: User ID or Password invalid. ERRORCODE=-4214, SQLSTATE=28000

Open in new window


I thought maybe that user is now allowed to log in remote - so I create another user, lets say user123.  I added them to the db2inst1 group (which I think means they should have admin rights), I also get same message when I try to login to that user via squirrel.
0
Free Tool: Site Down Detector
LVL 10
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi,

  I have a Stored Procedure, which returns 15 fields.
I wanted to capture the results from that SP, store it in a Global Temp table, and filter for a where condition.
When i call the second SP, it is not returning all the records it's supposed to return.

Attaching the code i wrote. Can someone help me understand what the issue may be?
0
I Gurus,

I want to design a script which will take last 12 hours using timestamp with specified date not like current time stamp and after 5 min if i run  the script it will take updated records and also it will delete the records from last 5 min.
Like a queue for ticket counter.

Thanks in advance any help will be appreciated.
0
does entity framework 6 will support ibm db2?
0
I am trying to implement a solution to the problem using SQL (DB2).

A month is defined based on below condition :
If 1st of month is on a Wednesday or earlier, then that corresponding Monday marks the beginning of the month
Say : Mar 1 2017 was a Wednesday so the month 201703  will start from 28th Feb.
If 1st of month is on a Thursday or later, then the next Monday marks the beginning of the month.
Say :  Apr 1 2017 is a Saturday so the month 201704 will start from  3rd April.

Now based on the logic,  I need to update the month for each and every date in a table. The date could be of any value. Accordingly to above month definition, I should update the month value.

Say 28-03-2017 should belong to 201703 , 01-04-2017 should still belong to 201703 etc.

could someone advise.

thanks
0
I have used below lines to execute the stored procedure. But it didn't return any results. could you please help me regarding that.

proc sql;
   connect to db2;
   execute (call "DBAB.SP10");
quit;
0
Hello. I am trying to create a crosstab query in QMF/DB2. I know that there is no Transform function so can this be done in QMF/DB2?
The fields I'm working with are Store#, CSG, and Order#. I would like the row headings to be the Store#, the column headings to be the CSG,
and I want to calculate the count of Order#.
Thanks,
James
0

DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Top Experts In
DB2
<
Monthly
>