Hot answers tagged db2
15
All of the platforms you have mentioned can run close to zero data loss configurations. All of them could be deployed in a configuration that will fail.
Platform choice is one part of the puzzle. It will be your implementation of the platform that determines whether or not your requirements are met.
MySQL Cluster
Oracle RAC
PostgreSQL High Availability
...
7
First of all, if this is homework, please tag it as such.
Secondly if it's not homework and you're doing this in a professional environment, get a professional to do it (or at least to thoroughly scrutinize your final design). Schema design underpins your application design, and flows on from clarity in business requirements and how well you understand ...
6
First off, the {1}s are redundant, so it's really just:
^[aofdmep][a-z][a-z0-9]{4}a[sidbfkfpo]
This is actually a pretty simple patterh ... it's just looking at what the first 8 characters of the string are, and it's always a fixed length string, so you could possibly build a table of all of the permutations, and then do:
WHERE SUBSTR(string_to_match, 1, ...
6
Disclaimer: I do not know DB2.
I simply googled these with "db2 table definition".
Source:
SELECT *
FROM SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL
WHERE TAB.CREATOR = COL.TBCREATOR
AND TAB.CREATOR = 'xxxx'
AND TAB.NAME = 'xxxxxxxxxxxxx'
AND TAB.NAME = COL.TBNAME
AND TAB.TYPE = 'V' ( OR 'T' )
ORDER BY 1,2;
Source:
SELECT * FROM syscat.tabconst ...
6
Yes, it is a valid physical design modification to accommodate more flexible locking behavior.
If you join the two tables and expose it to your ORM with a view, I don't think the ORM will know the difference (unless it attempts to look at the view metadata like looking for primary key or something during code generation).
Your view should be updatable, and ...
6
I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.)
The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water ...
5
I've just come across StandardCDC which might be of interest:
StandardCDC captures data manipulation language (DML) changes for a specified table and stores the results in a relational format. The capture table mirrors the column list of the tracked object, with options for storing only specific columns.
5
Here is the information on the CREATE INDEX statement for DB2. You may also want to check these links on designing indexes and space requirements for indexes as they also deal with your question on what factors into index page space.
Based on the last link it would seem to me that the page size for the index would be your column lengths + the index ...
5
I don't think you can avoid a local install. I'm not aware of any online sites (e.g. SQLFiddle does not offer DB2).
In addition to the book you linked to, I can highly recommend the "DB2 SQL Cookbook" as well: http://mysite.verizon.net/Graeme_Birchall/id1.html
And don't forget the official manual: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp
5
I'm not sure of db2 syntax, but how about this?
-- declare your 4 variables
@START_ROSDAT
@STOP_ROSDAT
@START_ROSTIM
@STOP_ROSTIM
select *
from PHONELOGTABLE
where ( @start_rosdat <> @stop_rosdat -- search covers more than 1 day
and ( (ROSDAT > @START_ROSDAT AND ROSDAT < @STOP_ROSDAT) ...
5
Well, the trick is that a database can only specify which "locale" it is used for at creation time. When you create a database you either specify what you want by specifying the codeset, territory and collation (example CREATE DATABASE MYDB AUTOMATIC STORAGE YES ON '/data' DBPATH ON '/dbdir' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM), or you let ...
4
Triggers and audit/history tables. And columns in the main table for Inserted/Updated
A history table is easily queried and can be demonstrated to an auditor
Universal across different RDBMS
No history write = no write because the trigger is part of the transaction
From the audit/history tables you can get the state of the database at any point in time.
...
4
SELECT * FROM
(SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1)
WHERE substr(MyString,1,1) = 'a'
AND substr(MyString,2,1) IN ('a','o','f','d','m','e','p')
AND substr(MyString,3,1) BETWEEN 'a' AND 'z'
AND (substr(MyString,4,1) BETWEEN 'a' AND 'z'
OR substr(MyString,4,1) BETWEEN '0' AND '9')
AND (substr(MyString,5,1) BETWEEN 'a' AND 'z'
OR ...
4
I have administered both MySQL and DB2 database systems. In general, I'd say that DB2 requires more memory and more administration. But sometimes, you can use DB2's sophisticated features to perform queries which are much more efficient.
About memory and administration: DB2 is not very good at making good use of RAM. Either you spend a lot of time ...
4
Found the answer here.
I needed to run the command
db2 rollforward db database_name to end of backup and complete;
This commits everything to the logs and places the database in a no longer pending state, thus allowing connection to it. I needed to do this for each database restored.
EDIT: Found this nifty nugget while attending a DB2NightShow ...
4
I believe REPEAT will give you what you are looking for.
First example from the page referenced above:
Example 1: Repeat 'abc' two times to create 'abcabc'.
SELECT REPEAT('abc', 2)
FROM SYSIBM.SYSDUMMY1;
4
If you plan to use the index for equality, you could add a checksum computed column:
alter table line add value_checksum as checksum(value)
create index IX_LINE_VALUE_CHECKSUM on line(value_checksum)
You can then select like:
select * from line where checksum_value = checksum('search text here')
4
If I understand you right, you are going from DB2 10.1 Express-C edition to DB2 10.1 Work Group Server Edition or Enterprise Edition.
If that is case then you shouldn't have any issues. The binaries for DB2 are basically the same across editions. It is just a matter of a license file that enables or disables certain features and enables/disables use of ...
4
I think you are missing couple important parameters. If you believe the project will advance , you have to keep in mind that one day you will need to switch to non-express version. And at that point license cost becomes very important; switching to another RDMS may require serious refactoring (and usually it does). For startup project I'd not pay too much ...
4
Yes, that is how you would query for all the backups that were taken by your database.
If you don't set AUTO_DEL_REC_OBJ = ON for your database configuration (as well as NUM_DB_BACKUPS and REC_HIS_RETENTN) then, yes, it will show backups that were deleted from disk.
You can also use the prune command to clean out backup entries (ans their linked backup ...
4
using this SQL you could query in an easy manner the history file
SELECT start_time, entry_status, operation, operationtype, backup_id,
devicetype, OBJECTTYPE
FROM SYSIBMADM.DB_HISTORY
WHERE operation in ('B','R')
ORDER BY start_time DESC FETCH FIRST 30 ROWS ONLY
if you're using tsm you could use
db2adutl query db DBNAME
4
By default the global temporary tables are created with the option ON COMMIT DELETE ROWS. Whatever tool you are using to run your statements must have the autocommit option turned on, so as soon as you issue the INSERT statement it is committed, thus deleting rows in the table.
You should either create the table using the ON COMMIT PRESERVE ROWS option, or ...
3
Change tracking can be used in Standard Edition. It will allow you to track changes to the data, but won't be able to tell you who made them.
Your options are basically the following:
triggers - maybe even use instead of triggers - for an update you can move the current copy of the row to a history table, and replace it with the active copy.
move to ...
3
Designing a platform: one database or multiple databases? is relevant background reading for your question.
You're possibly approaching this from the wrong angle. Is there really going to be a single, all mighty, all encompassing, one true system called "THE Hospital Management System"? Or will there be:
Appointments Management System
Patient Records ...
3
This question is somewhat old, so you may have already figured out your problem, but if I may offer an alternative...
It may be better to use a MERGE statement in this situation (click the link for the publib entry):
MERGE INTO mySchema.myTable tab USING (
VALUES ('abc', '123')
) AS merge (C1, C2)
ON tab.column1 = merge.C1
AND ...
3
You could define a trigger in a suitable language to examine the data and block storing if validation errors occur. At least Postgres users could take this route.
For DB2 I found an example on how to use XML Schema for validation of XML columns. There should not a big difference to call XMLVALIDATE from within a check constraint or a trigger.
The example ...
3
This is a long-standing bug in the GUI tools.
However, as long as your password does not contain a double quote (") character, then you can work around this by putting double quotes around the password to connect.
connect to sample user test using test!
--> SQL0104N
connect to sample user test using "test!"
--> connection succeeded.
This method ...
3
I think: general to general, specific to specific. If you are planning to add some table and this data is specific to nurses ONLY, so make reference to nurses. If it is a permissions list, use reference to general staff table or better users list. Generalization is a way to organize that we want to organize. In life it's more complicated.
3
Did you check the db2 diag log? what does it say?
The first thing comes into my mind it is that probably, the database does not rollback when the transaction log is full, but instead your updates never finishes.
Check the database logging parameter to see if there is enough log space to record the transaction.
BLK_LOG_DSK_FUL ...
3
Well, DB2 has a backup/restore system, and this allows to make full, incremental and delta backups. For more information about this, you can take a look at: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0005945.html
However, if you want to do a clone of the database, by just copying the data files, you could ...
Only top voted, non community-wiki answers of a minimum length are eligible
