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.
|Dude, where's my php.ini?
So a few years ago I installed apache 2.2x and php 5.3.1 on a Linux server I maintain. I used .tar.gz's and built them as instructed (instead of rpms and what-have-you). And all was fine.
Today I need to install this http://us2.php.net/ibm_db2 which seems like a php library. I went through all the steps up to make install, and I find ibm_db2.so in $PHP_HOME/lib/extensions/somecomplicatedname/ibm_db2.so
The great catch is the last step is to configure php.ini but there is NO php.ini on my system. Horror of horrors. php works fine, except of course for this new-fangled ibm_db2 thingamagic that I want to use so somebody can use a GUI to tinker with DB2. (I tried a small php script which fails and indicates that the ibm_db2 functions are not available).
I have to deal with PHP once every few years, so please enlighten me at a very basic level about what I could do to enable web-based GUI access to DB2. Thank you in advance!
Hibernate can't instantiate id generator in Spring project with multiple data sources
I have a Spring project using Hibernate with two data sources (db2 and sql-server).
As soon as I add
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SOME_SEQ")
to a column field in my entity class, I get a BeanCreationException when creating the sql-server EntityManagerFactory because org.hibernate.dialect.SQLServerDialect doesn't support sequences. The only place this entity is being used, though, is in a DAO that uses a db2 EntityManagerFactory which is using the appropriate dialect.
What am I missing?
Strange update behaviour
Recently I began upgrading a sql server (2005 32 bit) to 2012 x64. The server has a linked server on it pointing to an IBM ISeries db2 database.
I attempted to recreate the linked server on my 2012 machine using the following command:
EXEC master.dbo.sp_addlinkedserver @server = N'SERVERNAME', @srvproduct=N'DB2 UDB for iSeries', @provider=N'MSDASQL', @datasrc=N'SERVERNAME', @provstr=N'CMT=0', @catalog=N'LIBNAME'At first glance it appeared to be working fine. However, during testing I began noticing some strange behavior.
Running this query returns "5 rows affected", as it should, yet upon viewing the data nothing has actually changed. It does not return any errors, and this exact query works as expected on my original (2005) machine. The DB2 database has not changed at all either.
Update [SERVERNAME].[SERVERNAME].[LIBNAME].[FILENAME] set ABCID = newid() where ABCID = ' ' and UPLTS = cast('1901-01-01' as datetime)ABCID is a char field, primary key. UPLTS is a datetime.
Below are the properties of the linked server.
Any ideas? I'm stumped...I've researched a lot but haven't found anyone else who's having this problem, nor any ideas about how I might be able to debug it.
EDIT: I also seem to constantly get these errors when I try to update using a key or date field in the where clause (see below):
update SERVERNAME.SERVERNAME.LIBNAME.TABLENAME setfield1 = upper(field2)from Localtable where id1 = id2and ((UPLTS <> CAST('1901-01-01' AS DATETIME)) AND (STRTS <> CAST('1901-01-01' AS DATETIME)))OLE DB provider "MSDASQL" for linked server returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".The OLE DB provider "MSDASQL" for linked server could not UPDATE table. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
In DB2 for LUW, can I return all associated rows in a join if only one row matches
Let's say we have Table A and Table B. Technically, this is a many-to-many relationship. One entry in A can be associated to multiple entries in B, and vice versa. My issue arises when I have a subquery that returns a set of B entries. These are matched to A, but I would also like to get ALL entries in B that are associated to A, which is likely a superset of the subquery. As a result, I have to join A back to B. It should be noted that I need information from both A and B to be returned, which excludes a number of possible techniques.
What I'd like to do is start with A, join to B, and then filter with the subquery in such a way that if an entry in B is matched in the subquery, then ALL entries in B associated to the entry in A will be returned.
For example, let's say entry A1 is associated to B1, B2, and B3. If our subquery contains B1 and we join from B to A, then we'll get one row with A1 and B1. If we join back to B, we can get A1 with B1, B2, and B3 in three rows. What I'd like to do is find a function, expression, etc that can join A to B, then see that B1 is in the subquery and that B1 is associated to A1, so all rows for A1 are returned and we get B1, B2, and B3 in three rows. If A2 is associated to B4 and B5 and neither of them are included in the subquery, then A2 should be excluded from the results. Basically, one hit in the association returns all rows for the association. In this way we can treat it as a one-to-many relationship from A to B.
To diagram it:
A | B----------A1 | B1A1 | B2A1 | B3A2 | B4A2 | B5Where A1 and B1, B2, and B3 are both keys (and are actually two columns of randomly-generated bigints, but this simplifies things a bit) that are present as foreign keys in the other table. In the first example:
WITH SUB (ID) AS (Subquery)SELECT bb.*, aa.*FROM SUBJOIN B bbON bb.OTHER_ID = SUB.IDJOIN A aaON bb.ID = aa.IDWhich, when the subquery returns B1, will give us:
A | B----------A1 | B1Now, if we append this to the query and change the SELECT to aa., bb2.:
JOIN B bb2ON aa.ID = bb2.IDThen we get all rows for A1 as seen in the first example table. However, this requires joining back to B, which I would like to avoid. Also note that the subquery does NOT return B.ID, but a separate identifier, B.OTHER_ID. Instead, consider this:
SELECT aa.*, bb.*FROM A aaJOIN B bbON aa.ID = bb.IDWHERE--Insert function here that will take the subquery, and if any row in B --is present in the set returned by the subquery, then ALL rows in A that include --at least one matching row will be returned with all related rows in BThis is a simplification of what I'm actually trying to do, but the reason is that it's simply to expensive for me to join B to A, then A to B again (there are a few tables in between due to normalization). Is there a way to do this by only joining A to B or am I wishing for something that doesn't exist? I'm not looking to rewrite the query to enhance readability. I'm just looking for a way to logically process the relationship to reduce the number of necessary joins.
Sorry for asking this more than once, but I don't think I've been able to quite convey properly what I'm trying to do. Maybe it just isn't possible.
Using DB2 and jdbc on Mac OS X
i'm using Mac OS X Snow Leopard and IBM's DB2.I have it up and running, i already imported a schema.sql and can manipulate the database via SQL-Commands.For connecting to the db in java i need the jdbc Driver from IBM, so i googled it and downloaded the correct version from this site: http://www-01.ibm.com/support/docview.wss?uid=swg21363866
Then i extracted it and pasted the db2jcc.jar into my Eclipse Projects Folder.I configured my Buildpath and now it is listed as an Referenced Library.FYI: This is a exercise from college. So we were then adviced to copy-paste this class:http://pastebin.com/3GYXdnxuI did that and wrote a main method calling for openConnection. Now i am getting:
java.lang.ClassNotFoundException: COM.ibm.db2.jdbc.app.DB2DriverMy password, as well as my username are correct.What am I doing wrong.Do i need to set some kind of classpath on Mac OS X?
The DB2 SQL script is hanged in shell script, but work successfully in command line
I have a DB2 script to first drop and then create some table spaces and functions. I can run the SQL script successfully in DB2 command line on the targeted database.
I need to execute this SQL script in a shell script multiple times. It can be executed successfully the first time, then it will hang at the second/third time. The command to execute the SQL script is very simple:db2 CONNECT TO ktest4db2 -v -f /tmp/sql/application_system/opmdb2_privilege_remove.sql.5342
I use DB2 9.7.8, and LINUX operating system. When the SQL script is hanged, I can still manually run the SQL script successfully in DB2 command line on the targeted database.
Does anyone know the reason? Thanks.
Call QMF procedure from .NET
We're doing Windows development against a DB2 database with QMF v9.1. We hard-code many of our SQL queries directly into our applications, even though they are already stored in QMF.
However, I was told that a built-in stored procedure exists that is able to return results from a QMF query or procedure.
Would anyone be able to share their experience in regards to the above? Many thanks in advance.
How to efficiently retrieve data in one to many relationships
I am running into an issue where I have a need to run a Query which should get some rows from a main table, and have an indicator if the key of the main table exists in a subtable (relation one to many).
The query might be something like this:
select a.index, (select count(1) from second_table b where a.index = b.index) from first_table a;This way I would get the result I want (0 = no depending records in second_table, else there are), but I'm running a subquery for each record I get from the database. I need to get such an indicator for at least three similar tables, and the main query is already some inner join between at least two tables...
My question is if there is some really efficient way to handle this. I have thought of keeping record in a new column the "first_table", but the dbadmin don't allow triggers and keeping track of it by code is too risky.
What would be a nice approach to solve this?
The application of this query will be for two things:
Indicate that at least one row in second_table exists for a given row in first_table. It is to indicate it in a list. If no row in the second table exists, I won't turn on this indicator.To search for all rows in first_table which have at least one row in second_table, or which don't have rows in the second table.Another option I just found:
select a.index, b.index from first_table a left join (select distinct(index) as index from second_table) b on a.index = b.indexThis way I will get null for b.index if it doesn' exist (display can finally be adapted, I'm concerned on query performance here).
The final objective of this question is to find a proper design approach for this kind of case. It happens often, a real application culd be a POS system to show all clients and have one icon in the list as an indicator wether the client has open orders.
Unable to connect to DB2 server via db2cmd
I'm unable to connect to db2 server using db2cmd command
db2 connect to mydb user myuser using password
Getting the following message
SQL8002N An attempt to connect to a host failed due to a missing DB2 Connect product or invalid license. SQLSTATE=42968
But I have successfully connected to this database using jdbc and I can view the structure.
Has anybody experienced such issue?