Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

DB2 10.5 on Windows

OK, this is killing me.  It should be a simple thing but I have hit a wall and am not sure what is going on.  On my development database, I am issuing the following command.

db2 backup db Database1 online to z:\db2backups compress without prompting

Open in new window


But after a minute or so I get this.

The requested command or operation failed because the user ID does not have the authority to perform the requested command or operation:  User ID "MyUserID"

Open in new window


MyUserID should have SYSADM rights on that database.  It is a domain user and it is an Admin on the windows server.  So I don't understand what the issue is.  I also made sure MyUserID has full privileges on the Z: drive.  

How can I be sure MyUserID is SYSADM and what might I be missing?  This is really killing me because it should not be this hard to backup my db.  What am I doing wrong?  This works fine on my production server, but it uses a different user.

Thank you.

Jim
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

I currently use Access to generate reports from MySQL and DB2 databases. I'm basically using Access as a reporting frontend using Pass Through queries. Is it possible to automate report generation from outside of Access, maybe through the use of an API?

I have reports that need to be generated as events take place in the database. For example: if an order comes into our DB then a pretty PDF copy of that order needs to be sent via email back to the customer.
0
I've been tasked with updating remote MS SQL database tables. I'd like best ideas for utilizing our IBM i to do this. I'm primarily and ILE-RPG, RPG Freeform programmer, but have worked using RPG-XML (which we have) and consuming  web services via a local MS SQL database. I'm also familiar with SQLLERPG to a degree.    

I have access to 'data source', 'Initial Catalog', 'User ID', and 'password' for the remote database tables.

All ideas are welcome and will rewards points accordingly.

Thanks,

Phil Knox
0
But whenever possible have a unique clustered index.  For example, on the state codes table, a unique clustered index on state_code would work just fine.
i am reading as above.
what is meaning of unique clustered index and how it is different from regular index. please let me know advantages of using it.

please advise
0
DB2 10.5 on Windows

I have written a stored proc that checks the condition of all my indexes and if they need to be REORGed then I do it.  Here is how I check the indexes per schema.

     
      CALL REORGCHK_IX_STATS('S', 'Schema1');

     INSERT INTO MAINT.INDEXSTATS_TABLE( 
          TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,INDCARD, NLEAF,REORG_FLG)
     SELECT 
          TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,INDCARD, NLEAF, REORG
     FROM SESSION.IX_STATS WHERE REORG LIKE '%*%';

Open in new window


When I look at the index stats table I created, I see this.

     
ID       Schema       Table                       INDEX                                INDCARD   NLEAF     REORG
534    SCHEMA1   SESSION_TOKEN	SESSION_TOKEN__IDX     530320	4073	     *----

Open in new window


So I run this.

     INSERT INTO MAINT.INDEXSTATS_COMMANDS (SQL_COMMAND)
     SELECT DISTINCT
               'REORG INDEXES ALL FOR TABLE ' 
     		|| RTRIM(SUBSTR(TABLE_SCHEMA,1,11)) || '.' || RTRIM(SUBSTR(TABLE_NAME,1,31)) 
     		|| ' ALLOW WRITE ACCESS ' 
     		|| CASE 
     			WHEN SUBSTR(REORG_FLG,4,1) = '*' 
     				THEN 'CLEANUP ONLY' 
     			WHEN SUBSTR(REORG_FLG,5,1) = '*' 
     				THEN 'CLEANUP ONLY PAGES' 
     			ELSE '' END 
     		|| ';' 
     FROM  MAINT.INDEXSTATS_TABLE 
     WHERE INDCARD > 10 AND  NLEAF > 10
     WITH UR;

Open in new window


To create and run this command.

REORG INDEXES ALL FOR TABLE SCHEMA1.SESSION_TOKEN ALLOW WRITE ACCESS ;

Open in new window


It runs and seems to work, but when you check the exact same index using REORGCHK_IX_STATS five seconds later,  it has not changed at all.  All the stats are the same.  If an index has a REORG status of *----- does it not need to be REORGed?  I am somewhat confused.

Due to this, the same 50 tables are being REORGed every night.

Any help would be great.

Any Merry Christmas and Hapy Holidays to everyone!!!

Jim
0
Dear experts
We are looking for a mechanism to export table content from oracle and import in db2 on a mainframe.
We would prefer to produce fixed record dumps from oracle which can be imported by the mainframe db2 loader utility.
The preferred solution would be to use: expdp, it would be great to get a sample config file to achieve fixed record length export which is importable for db2
as plan B we would have to fall back to write a small java pgm, but I'd prefer option A
Many thanks for your support
0
I have a database in db2 that needs to be migrated to mysql. I ma expoting data from db2 to csv and then csv to msql. the data is in multiple langugaes like Chinese, French, Latin, Hebrew, etc. Due to character set issues, the data is converted into symbols. How shall I ensure that the import is done correctly without any changes in the data
0
DB2 10.5 on Windows

How do most people schedule DB2 scripts to run?  I would like to be able to run certain maintenance scripts to run at 3:00 am nightly.  

I have created a batch file that calls another batch file that runs the DB2 cmd window.  The script in the second batch files creates another file of SQL commands that is then run.  The whole thing is run from the windows task scheduler but there are some issues.  

1.  I hate batch files and having to have 2 seems silly.

2.  The files run fine when I run them but fail more often than not when called from the windows task scheduler.  (various reasons not the focus of this post).  The problem is in running the 3rd script file.

3.  I have to put the username / password in the bat file in clear text.

So my question is how do most DB2 DBA's do this on windows?  SQL Server has a nice built-in job agent that makes it a breeze but DB2 does not seem to have that ability.

Is there an easier way to do this?  Am I missing something obvious that would make my life simpler?  Is there a third party application that will run as the SQL Server Agent with DB2?

Thank you!

Jim
0
Hi all

My Db2 DB (Disk type-Non TSM) backups are running slow.

I could see Memory is 60 percnt used and CPU is 50 % idle

I have seen disk utilization with topas and iostat but could make any concrete judgement that Disk utilization is slow.

But i have strong suspect-ion that there may be some problem with disk utilization.

It is a AIX machine.

Need some advice here

Thanks in advance!!!
0
I have an issue with my Exchange 2013 on 2012R2. The Content index files \ms\%default folder are consuming a large amount of space. We have a DAG with 3 databases.

DB1 - Size: 294GB, Index files are 140GB.
DB2 - Size: 29.53GB, Index files are 2.95GB.
DB3 - Size 128GB, Index files are 43.23.

With some searching I found that the DB1, and DB2 index folders seem to be a normal size which is 10% to 20%  ish of the size of the db. However, DB1 is closer to 50%. I have attempted a restart and update of the servers in the DAG, they seem to be growing faster than I can give disk space.
0
Get your problem seen by more experts
LVL 11
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Hi All

Does db2 move utility work for different environments if they are on different servers (with different IPs)?

    db2move sample COPY -sn BAR -co target_db  target schema_map
      "((BAR,FOO))" -u userid -p password  

I need to do copy a schema.
need DB cataloging?

Connecting to database SOURCEDB... successful!  Server : DB2 Common Server V10.5.7

**ERROR - Failed to connect to target database : TARGETDB


db2move failed with -1 (debuginfo:19)

can you pls help
0
Hi All

Please let me know "How to Print SQLCODE and SQLSTATE info in the Procedure output when procedure fails
to process some input records.

So that i can understand the problem of issue.

Thanks in advance!!!
0
Hello
Could someone please help me to read and edit the text file through DB2 stored procedure
0
select count(1) into V_COUNT from schema.tablename where pay_id=V_PPCPAYINST_ID and attributename not like '%V_amount%'
with ur

here V_amount is PL/SQL DECIMAL or VARCHAR variable.

If use escape character (/) also it is not working.

how to pass the dynamic variable in LIKE condition

Can any one help me here.
0
I am facing the below error while connecting to DB with Toad client.

ERROR [08001] [IBM] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: .  Communication function detecting the error: "selectForRecvTimeout".  Protocol specific error code(s): "0", "*", "*".  SQLSTATE=08001

I have re-started the Toad, uncataloging and cataloging is done.

It looks toad is not viewing these settings.

Can any one help me to resolve this.
0
Need help on Type casting and removing of leading and trailing Zeros for amount column(Decimal) in DB2.

select cast(amount as VARCHAR(4000 OCTETS)) from schemaname.tablename where pay_id=1038 with ur ;

Casting function is not removing the trailing zeros.

Can you please suggest how to remove the leading and trailing zeros.

Thanks in advance!!!
0
why we need junction table for many to many.

i am not clear on that concept. can you please elaborate with example tables with data?
i am going through below link but not completely clear
https://www.youtube.com/watch?v=P_nhBKs25DQ
please advise
0
hi,
What is normalization concept


if a table called Student as below with 4 columns and data as below

Student table

StudentName YearOfStudy Subject TeacherName
john                    10thGrade    Maths    Ashley

why it is better to move away Teacher information to separate table as below

Teacher Table

TeacherName TeachingSubject
Ashley                 Maths


what is various types of normalizations?
Please advise
0
Hi All

Greetings.

I have used dynamic cursors in my code. I also have used elseif ladder to avoid following error.

The program attempted to execute an OPEN statement for an open cursor.

I could be able to resolve the above error with elseif ladder but the following statement in my code is not fetching any new record
into V_WC8PAY_ID when i checked in line by line debugging.

fetch c10 into V_WC8PAY_ID;

I could not understand why dynamic cursor c10 is not working.

Please guide me.
ext_data3.txt
0
Keep up with what's happening at Experts Exchange!
LVL 11
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

DB2 10.5 on Windows

I am trying to delete a lot of data from a table.  I created a duplicate of the table with a different name and am trying to move the last years worth of data to it, then truncate the original data and then move the saved data back to the original.

There is an ID column that is set up like this.

ID	INTEGER	NOT NULL	GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER)

Open in new window


When I run this to move the data back

INSERT INTO ORIGINAL_TABLE OVERRIDING SYSTEM VALUE (SELECT * FROM DUPLICATE_TABLE WHERE  DATE(LASTTIME) >= '01-01-2017')

Open in new window


I get this

ERROR [42601] [IBM][DB2/NT64] SQL0104N  An unexpected token "OVERRIDING SYSTEM VALUE" was found following "ORIGINAL_TABLE".  Expected tokens may include:  "<space>".

Open in new window


Everything I have read says the OVERRIDING SYSTEM VALUE should work but it does not.  I am sure it is a simple issue but I have wasted 1/2 a day on trying to figure it out.  Any help would be greatly appreciated!!

Jim
0
DB2 10.5 on Windows

Does anyone have any experience with Native Encryption for DB2 on Windows.  Or Linux?  From what I have read, it should be transparent to the users but I have to assume there is overhead involved somewhere.  

The main thing I have read is that backups can take up to 3x longer.

Any insights would be appreciated!

Thank you!

Jim
0
Hi,

I have a problem via SQL and into RPG too when I type SUBSTR(xxxx,1,6)
from some reason on my iSeries it requesting to be one space between comma and lengths e.g. substr(xxx, 1, 6).
I know that on other iSeries it's not a problem and on more examples it is not written with these spaces...

This is the error message when I check

Select * from ztrans0p                    
 where substr(trcrd#,1,6) = '516971'        
 Token ,1 was not valid. Valid tokens: ) ,.
   

Message ID . . . . . . :   SQL0104                                            
                                                                               
                                                                               
Message . . . . :   Token ,1 was not valid. Valid tokens: ) ,.                
                                                                               
Cause . . . . . :   A syntax error was detected at token ,1.  Token ,1 is not  
  a valid token.  A partial list of valid tokens is ) ,.  This list assumes    
  that the statement is correct up to the token.  The error may be earlier in  
  the statement, but the syntax of the statement appears to be valid up to    
  this point.                                                                  
Recovery  . . . :   Do one or more of the following and try the request again:
    -- Verify the SQL statement in the area of the token ,1. Correct the      
  statement.  The error could be a …
0
hi all,

Can DB2 has built in Brute-force protection ? what tools /configuration needs for this.
0
hi,

as MySQL configuration only allow one db log for ALL user DB. how about DB2 ? it should has one or more log file for EACH user database inside, right?
0
hi,

any one know what is the replication technology available for DB2?
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
>