Tag Info

Hot answers tagged

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 ...


10

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 ...


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 don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key: UPDATE ( SELECT t1.a AS t1a, t2.a AS t2a FROM t1 INNER JOIN t2 ON t2.b = t1.b ) u SET t1a = t2a ; Tested at (Oracle 11g) SQL-Fiddle Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the ...


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 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 ...


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 ...


5

The table MY_TBL contains large binary data in a BLOB column. The documentation of the REORG command says that DB2 avoids reorganizing such objects because it is time consuming and does not improve clustering. However, DB2 can be forced to reorganize LOB data if the LONGLOBDATA option is specified. The unused space can be reused by DB2, so inserting new data ...


5

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 ...


5

Some rules of thumb that might be relevant to you . . . Never try to tune a query without a representative EXPLAIN plan. Representative means to get an EXPLAIN plan either from the production database or from a dev/test database that have nearly the same number of rows and nearly the same distribution of values, ideally running on similar hardware. ...


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

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 ...


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

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

Unfortunately, it's not possible to take an online backup of a DB2 database if it's in circular logging mode, which is the default for DB2 databases when they are created. You can check whether your database is using circular logging by issuing: ./db2 get db cfg for dbemp | grep LOGARCH If both LOGARCHMETH options are switched off... First log archive ...


4

If you're using a recent version of DB2 on Linux, Unix, or Windows (LUW), you can use the db2pd tool to view all current transactions and how much log space they use (the LogSpace column): $ db2pd -d tst1 -transactions Database Member 0 -- Database TST1 -- Active -- Up 3 days 00:34:56 -- Date 2013-10-03-10.19.08.041274 Transactions: Address ...


4

You can only use SQL statements in a trigger. RUNSTATS and REORG are not SQL statements; they are DB2 CLP commands, and only the command line processor understands them. You can try using the system stored procedure ADMIN_CMD() to invoke those utilities, however, 1) it still may not work because of the nature of the utilities and 2) even it does work it ...


4

Try using the MERGE statement from SQL2003 Standard - which is the equivalent of UPDATE..SET..FROM syntax. Example: MERGE dbo.t1 AS Target USING (SELECT a,b,c FROM dbo.t2) AS Source ON (Target.b = Source.b) WHEN MATCHED THEN UPDATE SET Target.a = Source.a



Only top voted, non community-wiki answers of a minimum length are eligible