texts:
This is my first time using Linux. I am trying to install MySQL 5.6.15 on an Ubuntu box. I download MySQL from their website and put it on a USB stick then plugged the stick into the box.

How can I install it using the downloaded package and not the built in package?


texts: Does EnterpriseDB support Oracle RAC to Postgres replication?


texts:
At my current place of employment someone made a decision to put IDs into a comma delimited column.  I'm looking to break these out into a proper many to many relationship.  However;  it is not in the cards for the application that references this data to change right now.  So I need to modify a view to still show the comma delimited column.  What is the best way to setup the view so that it displays the comma delimited column?  Is there a way to do it that doesn't involve leaving the original column?

A stripped down version of  the table definitions is as follows.

ID
ProductName
FeatureIDs

ID     ProductName    FeatureIDS
1      Hot dog        1,5,4
2      Hamburger      1,3
3      Fish           2

ID
FeatureName

ID     FeatureName
1      Mayo
2      TarTar Sauce
3      Pickle
4      Relish
5      Onion

Ultimatly I'd like to combine these into a single products table and a many to many relationship

ID
ProductName
ProductType

where product type would be either 'product' or 'feature'

ID
productid
featureid

for the mapping table.

Yes the view that is setup needs to be an updateable view.


texts:
Is it possible in MySQL to know beforehand query execution time? I mean how long it will take to execute the query.

Generally its worthwhile to know how long any query can take to execute.

At the moment, problem at hand is Converting CHAR to BIT (1.2GB Table size, MyISAM, indexes not on the columns I want to change, 3GB RAM)


texts: Adding a date and the time portion of a datetime2 together

texts:
I have a date field [load_date] and a datetime2 field [load_time] that I want to combine into a datetime2.  I want to combine load_date with the time portion of load_time and create a datetime2.

It's in an existing table, and I want to SET the load_time to this new value.  Below is a SELECT that appears to work, so I can turn this into an UPDATE statement.  However, I wanted to know if there is a more elegant or less verbose way.

SELECT
    [load_date]
    , [load_time]
    , DATEADD(DAY, DATEDIFF(DAY, CAST(CAST([load_time] AS TIME) AS DATETIME2), CAST([load_date] AS DATETIME2)), CAST(CAST([load_time] AS TIME) AS DATETIME2))


texts: Display all parent records but only one record of child records of a parent record

texts:
I have encountered an issue while writing a stored procedure for displaying some records of a table. The following code is to illustrate the issue.

declare @t table(
    Id int primary key identity(1,1),
    ParentId int null,
    Item varchar(50)
)
-----------------------------------------------------------
insert into @t(ParentId,Item)
select null,'A' union all
select null,'A' union all
select 1,'B' union all
select 1,'B' union all
select null,'C' union all
select 2,'D' union all
select 2,'D'

The data after insertion in the table @t looks like this.

Id | ParentId | Item
---------------------
1     NULL       A
2     NULL       A
3      1         B
4      1         B
5     NULL       C
6      2         D
7      2         D

I want to select all records whose ParentId is null but select only the first record of those whose ParenId is not null.

Desired Result:

Id | ParentId | Item
---------------------
1     NULL       A
2     NULL       A
3      1         B
5     NULL       C
6      2         D

Any idea on how to get the desired result?


texts: Slight change of query causes CONVERT of illegal values to be done first, causing a conversion error

texts:
I have a table MyTable which has columns OldVal, NewVal, Nme which are all VARCHAR(MAX)

Wherever Nme = MyValue, the OldVal and NewVal values will be "integers" IE a numeric value value stored as a VARCHAR

Wherever Nme = SomethingElse, the OldVal and NewVal values will be "bigints"

Wherever Nme = SomethingElseAgain, the OldVal and NewVal values are string values

If I run the following query

SELECT   OldVal,
         NewVal
FROM     MyTable
WHERE    Nme = 'MyValue' AND
         CONVERT(INT,NewVal) < CONVERT(INT,OldVal)

The query runs as expected.

However If I change the query to

SELECT  1
FROM    (
            SELECT  OldVal,
                    NewVal
            FROM    MyTable
            WHERE   Nme = 'MyValue' AND
                    CONVERT(INT,NewVal) < CONVERT(INT,OldVal)
        ) a

I get an error

  The conversion of the varchar value '3666999000' overflowed an int
  column.

The value in question in the error message is one where Nme = "SomethingElse" (which are all BIGINTs stored as VARCHARs)

it seems to me that what is happening here is that on the first query, the optimizer first filters by Nme = 'MyValue' and then does the conversions on the residual data and the second query tries to do the conversion first (meaning it tries to convert values that are illegals INTs) and then applies the Nme = 'MyValue' filter.

This is confirmed by the predicate in the tooltip on the filter operator in each (Estimated) execution plan

The database is on a 2014 server and running in Compatibility Level 110. The behaviour is still present if I force the 2014 optimizer with OPTION (QUERYTRACEON 2312)

What would cause this to happen? I suspect maybe something to do with wrapping NewVal and OldVal in a CONVERT is causing issues with statistics but surely that would have affected both queries?

NB - above is abridged code narrowed down to its simplest form for the purpose of demostrating it here
also, I know storing ints as varchars is not good practice but this is a third party database which we cannot control the design of


texts: Oracle 10g Listener issues after IT changed computer domain name

texts:
I need some help here.

We have Oracle 10.2.0.5.0 installed on a windows 2008R2 machine.

The issue is that our IT department changed the domain for that machine.
I tried editing our tnsnames.ora and listner.ora files and restarted the listener service.

I can connect to the database remotely using sqldeveloper, but if I try to connect through one our legacy apps that uses BDE I'm getting ORA-03114: not connected to ORACLE.

In addition to this when I try to do a imp command or try to sign in using the sqlplus command on the box I get the following error.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

So I believe I'm missing something a step here.  I've tried this as well
http://www.oralytics.com/2011/03/changing-domain-of-oracle-database.html
But I received a warning trying to drop the repo, and received an error trying to create it.

lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.5.0 - Production on 24-JAN-2018 10:48
:20

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.5.0 - Produ
ction
Start Date                24-JAN-2018 10:39:51
Uptime                    0 days 0 hr. 8 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File         C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MYCOMPUTERNAME.myNewDomain)(POR
T=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.myNewDomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.myNewDomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT.myNewDomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

listner.ORA file

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = MYCOMPUTERNAME.myNewDomain)(PORT = 1521))
    )
  )

TNSNAMES.ora

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYCOMPUTERNAME.MYNEWDOMAIN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Update:  Added my SQLNET.ORA file
    # sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAMES.DEFAULT_DOMAIN = MYCOMPUTERNAME.MYCOMPUTERDOMAIN

I appreciate any help you can give me.


texts: How to get only the strings before a character in SQL Server

texts:
I am trying to write a sentence that gets only the strings which are just before a certain character.
for example:

DECLARE @TEST VARCHAR(MAX)
SET @TEST =  'CAMP1 = 4 AND CAMP2 = 5 OR CAMP3 = 7 AND CAMP4 = 9'

I am trying to to parse the string @TEST and get only the camps one by one and check if this camps (CAMP1, CAMP2, CAMP3, CAMP4) realy exist.

I don't know if loop would be necessary to sweep the string and get the strings before '=' and after the logic operators 'AND, OR'.

Any help pls ?


texts: Critical query plans removed from sys.dm_exec_query_stats while trivial items are kept

texts:
Running SQL Server Express 2019.
Have a complicated stored procedure containing 11 sql statements (when selected from sys.dm_exec_query_stats by plan_handle).
9 of those statements are simple variable setting based on passed-in parameters, the last 2 statements (IF/ELSE) do all the work and return data (e.g. query_hash 0x111... and 0x222...).
There's quite a variability in data volume based on input parameters, so I "prime" the stored procedure with optimal values to ensure quick execution.
I've noticed that every few days (sometimes more often), my last 2 statements (query_hash 0x111... and 0x222...) get dumped from sys.dm_exec_query_stats and then re-generated based on whatever parameters come from the users on the next stored procedure call.
I know this by looking at creation_time column, and it's reflecting the time of "priming" for first 9 queries, and a later time for query_hash 0x111... and 0x222... .
From what I understand, cached plans get cleared under memory pressure, and less used plans get cleared first. My queries are being used a lot, and I have a lot of other cached plans with 1 execution that stick around for a while.
Question: why are my most used queries get dumped form cache and how do I stop it from happening?


texts: Primary Key Constraint Violation in SSIS with Quotation Mark

texts:
Using SSIS Data Flow Task to transfer records from table in 1 SQL Server database to an IDENTICAL table in another SQL Server (2012) and getting "Violation of PRIMARY KEY constraint 'FLG_APVN_KEY_0'. Cannot insert duplicate key in object 'dbo.FLG_APVN'. The duplicate key value is ("ARIZ0010   ).".
In the Source table (FLG_APVN), there are two records with the following Primary Key values:   "ARIZ0010    and    ARIZ0010
Obviously similar but definitely distinct. The quotation mark at the beginning of the first PK Value seems to be getting dropped off by SSIS in the insert causing a primary key violation. I need both records in the destination table with their primary keys intact. How do I do this? (SQL Server 2012, SSIS from Visual Studio 2012).


texts: How to take Backup of BackUp DB in SQL Server 2012

texts:
In Our environment, Log log shipping is configured and working good.

Transaction logs are Backed-up on Primary Server.
Copy to Backup DB
Restore on Backup DB.

Background:

We are in process of changing the IP of Backup (Secondary) DB server. And before changing IP, client wants to take the backup of the Secondary(Backup) DB.

Question:

Is it possible to take the backup of Secondary Database?
(When I Right click on Backup (Secondary) DB --> Task --> backup, backup option is grayed out.)
--> Do I need to 'Take Offline' to take back up?
--> Or is there any other way?
Will there be any impact if we change IP of Backup (Secondary) DB and do we need to make changes anywhere else?
Will Log shipping keeps running without any issues, or do we need to reconfigure it as new?


texts:
I have 3 node cluster, problem is that for some rows , updatetime column have different value on 3 node: like :
1st node : updated_on          | 2018-08-22 17:01:30+0530
2nd node : updated_on          | 2018-08-22 17:02:30+0530
3rd node : updated_on          | 2018-08-22 17:03:30+0530

Please suggest , where i need to digging.


texts: backup maintenance plan fails with error 0x80131534

texts:
I recently set up a series of maintenance plans to backup all online databases. A full backup runs once a week, a differential runs once a day, and a transactional runs every ten minutes. We have two SQL Server instances on two machines. One works perfectly, backing up to another server on its network. The other, a Google cloud compute instance, is trying to back up to another Google compute instance, and it can't.

The SQL server in question, let's call it SQL1, is trying to back up to a Google server we'll call storage1. Storage1 is NOT a Google Storage bucket, just a full server with space to store the backups. Thus, standard Windows sharing should apply. I've made the SQLBackups folder on Storage1's hard drive fully shared to everyone on the network, so accessing it shouldn't be a problem. In fact, the plan can't be created unless the folder can be reached, so I know that's working. Yet, when I run the plan, it fails within ten seconds. The plan log isn't helpful, but the job log is more interesting: it reports that the job failed because it "could not load package. Error: 0X80131534. Description: no description available." Google has next to nothing on this error number.

The odd thing is, I ran into a similar problem while setting the same plans up on the other server, but it went away in the course of working out how to do all this so I never found out what exactly the problem was. I'm very confused, and in the meantime, our database isn't able to back up. Anything anyone can suggest is appreciated. Again, this is just the full backup job, and it's set to ignore offline databases and back up all databases. Thanks.


texts: how to get total count of partial counts

texts:
An extension to this Question here, how can I fetch the count for ALL the students who have reached their maximum allowed absents.

I tried this

SELECT COUNT(*) FROM
(
SELECT student_id,
       COUNT(attendance)
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                            FROM   configurations)
) abc

but it is not working and gives this error:

  No column was specified for column 2 of 'abc'.

Environment: SQL Server 2000


texts:
OLE DB connection...

Can anyone suggest me on this.

Authentication with windows security works in 2012-2005

  "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;
      Initial Catalog=TESTDB;Isolation Level = ReadUncommitted;Data Source=SERVER"

In 2012 this gives me an error in 2012

  Provider=SQLNCLI11;Persist Security Info=False;Initial Catalog=TESTDB;Data Source=SERVER;Language=us_english;User Id=AA;Password=SSS

error conn.open
Property access must assign to the property or use its value.
Sql client not registered


texts: Quering Postgresql digest function inside procedure works on pgadmin but not works by calling via Laravel

texts:
recently I have created stored procedure in Postgres Sql which deals about with hashing passwords. When used via PgAdmin tool it works like a charm as stated below.

I have also installed extension pgcrypto.

SELECT horeca_user.update_token(
    1, //user_id
    1, //type
    'sdsfsdfs' //token
)

It works when called via pgadmin tool, but not inside laravel app as stated below.

$token=\DB::select("SELECT * from \"horeca_user\".update_token('$type', '$user_id', '$token')");

and finally this is my procedure code

CREATE OR REPLACE FUNCTION horeca_user.update_token(tp integer,userid integer,token character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql' VOLATILE
    PARALLEL UNSAFE
    COST 100
AS $BODY$
DECLARE uid INTEGER;
DECLARE tok character varying;
BEGIN
    uid:=0;
    Select count(*) as total from horeca_user.user_tokens where user_id=$2 into uid;

    if uid=0 THEN
       Insert into horeca_user.user_tokens(id, user_id, tip) values($3, $2, $1);
     else
       Update horeca_user.user_tokens set id=$3, tip=$1 where user_id=$2;
     END IF;

     SELECT encode(digest(token, 'sha256'), 'hex') as tt into tok;

     return tok;
END

$BODY$;


texts: Fastest way to insert data into mysql from csv

texts:
I have a dataset of 233,902,846 rows in a csv file.
I want to load the data into mysql table. What is the most efficient way of transferring the data so that data insertion is the fastest. actually simple mysqlimport will take around 4-5 days for the complete insertion.

Regards
Mona


texts:
I have a TSQL script that counts transactions for companies based on all approved, declined and failed trans in the previous month(s).

I also show companies that have 0 approved, declined and failed using the line

OR 'datecolumnName' IS NULL

The script is missing companies that HAVE values in 'datecolumnName' but not for the month I am interrogating.  I want somehow to say where 'datecolumnName' is NULL OR NULL for 'This Month'

E.G Company ABC didn't transact in DEC but did in NOV so the OR 'datecolumnName' NULL script doesn't bring back any data for Company ABC

(Apologies if this is confusing didn't find it easy to explain)

In response to Squirrels message I have attached the code I am running anonymized

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
DECLARE @startOfPreviousMonth DATETIME
SET @startOfPreviousMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

SELECT Object1.Column1 AS CompName,
       SUM(CASE
               WHEN Object2.Column3 = '00'
               THEN 1
               ELSE 0
           END) AS Column4,
       SUM(CASE
               WHEN(Object2.Column3 != '00'
                    AND SUBSTRING(Object2.Column3, 1, 1) >= 0
                    AND SUBSTRING(Object2.Column3, 1, 1) <= 9)
               THEN 1
               ELSE 0
           END) AS Column5,
       SUM(CASE
               WHEN(Object2.Column3 != '00'
                    AND SUBSTRING(Object2.Column3, 1, 1) >= 'A'
                    AND SUBSTRING(Object2.Column3, 1, 1) <= 'Z')
               THEN 1
               ELSE 0
           END) AS Column6,
       Object1.Column7
FROM Object3 Object1 WITH(NOLOCK)
     FULL OUTER JOIN Object4 Object5 WITH(NOLOCK) ON Object1.Column7 = Object5.Column8
     FULL OUTER JOIN Object6 Object2 WITH(NOLOCK) ON Object5.Column9 = Object2.Column10

                            WHERE Object2.Column11 >= DATEADD(month, -1, @startOfCurrentMonth)
                            AND Object2.Column11 < @startOfCurrentMonth
                    OR Object2.Column11 IS NULL

     GROUP BY Object1.Column1,
         Object1.Column7
ORDER BY Object1.Column1

This is the output

The rows with all '0' means that company has never transacted but if a company has transacted in a different month the row is ignored.


texts: Calculate percentage of records that have an association in another table

texts:
Table A                                           Table B
Columns                                           Columns
Performance Count                                  Key
Source                                             Record
Date
Key

I need the %age of Total Performance counts for a Source and Date that have a value in the record column of Table B

select sum(a.Performance Count) As TotalPerfCount, a.Source, a.Date,
(100 * b.Record / a.sum(a.PerformanceCount)) AS Percentage
from Table A a
JOIN Table B b ON a.key = b.key
where b.Record is not null
Group By a.TotalPerfCount, a.Source, a.Date

This isn't pulling all records that have a value in b.Record.  what am I doing wrong?


texts: Oracle Database Enterprise Users & Roles with Microsoft Active Directory?

texts:
We have a legacy application and we have full access to source code and platform running it, so virtually any change can be implemented if required.

The application is a Java Swing heavy-weight GUI (desktop) accessing directly the database through JDBC in a 2-tier fashion. At application start-up, the user enters his/her credentials consisting on user&password. The current security schema is:

Oracle Database 10g Enterprise (migrating to 11g) with Oracle Internet Directory 10g (OID).
Oracle uses Enterprise Users so when connecting to DB, user provided in connection comes from GUI and Oracle validates against OID.
DB permissions are managed through Enterprise Roles. Each ER defined in DB maps a Group defined in OID, so user DB access to objects (tables, procedures...) are controlled by the Groups he/she belongs in OID.
Several GUI users can create others users, so create they in OID, assigning to the corresponding OID Groups in order to grant DB permissions. This is done using special Oracle permissions in OID to this "super-user". This is done from GUI using standard LDAP API, not OID API. The GUI user connects to OID with its credentials and performs LDAP operations.

The idea is to replace OID with Microsoft Active Directory, so the database would validate user against AD. DB permissions defined in ER would be retrieved also from AD, based on the Groups the users belongs to.

Assuming Oracle DB 11g and MS Windows Server 2008 (or upper versions if necessary), is that possible?

Restrictions/notes:

Oracle is installed on Linux, RH typically.
Oracle Virtual Directory can not be used nor any Identity Management Oracle product, just Oracle DB Enterprise.
GUI application executes on Windows workstations that are already in the Windows domain with the AD, son can use their OS credentials instead custom users used by GUI, but this must be done in Java (any version supported).
SSL, Certificates are not the preferred way, as they required to be provisioned.
Kerberos with MS KDC is also not the preferred way.
The new security schema with AD could lead to a less secure environment, but this is acceptable.
Prefer not to add third-party products to the security schema.
Oracle DB - MS AD integration should be supported by Oracle and Microsoft.

We need some advice from people with previous experience in similar installations about if it is possible the Oracle - AD solution. Some guidance and steps are welcome!


texts:
select t.*, row_number() over (
    partition by t.Entity_Identifier
    order by t.Message_Sequence
    ) as seqnum
from Process_Activity t (ROWLOCK)
where t.Process_Id =

Is it possible for this select to retrieve a different number of rows based on whether it can lock a row or not?

We have a situation where the select will bring back 18 rows when there is another process performing an insert/update on the same table, but the select will bring back 33 rows (the correct amount) when the other process is not running.

I feel the ROWLOCK is getting in the way. Why use a rowlock on a select?


texts: How can I change default location for tablespaces

texts:
In Oracle, how can I find out where datafiles for a tablespace are created by default if I do not provide a full path for the datafile but just the name?
And how can I change this default location?


texts: Changing a user from being linked to a login to being without login

texts:
I can create a user like this:

create user [foobar] for login [foobar];

I can also create a user like this:

create user [foobar] without login;

Is there a way to change a user that was created with the first method into a login-less user as though it had been created with the second method?

It seems that the opposite isn't possible (i.e. mapping a login-less user to a login) and it's not immediately clear to me why that would be the case. So if anyone can shed light on the reasons for that as well, I'd consider it a bonus.


texts: "truncate log on checkpoint" option in SQL Server

texts:
Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB option named truncate log on checkpoint. Their claim is that it prevents the scripts from functioning and performing backups on the instance which is SQL 2012.

I feel that's complete BS since I cannot find any such option via sp_configure and backups are working everywhere but one instance. However I would like to remove a land mine, if that's what it is, and remove other obsolete elements. I do have them checking with the vendor but I don't have a high degree of confidence in anything that they say.

The research I did returned little more than it perhaps being an option for SQL 2000 or a Sybase option. Another claim was that it's called/used implicitly on later versions (2008 and up) when the recovery model is SIMPLE and there's no explicit option to turn it on or off.

Since the TRUNCATE LOG command is obsolete due to how transaction logs work these days, I think it's not an option that can even be queried at this point.

As I don't have any SQL Server 2000 instances about, I was hoping someone might recall this or could check it on one they have laying around. I've told them that it's nothing I can recommend. I was also hoping that someone can confirm this is obsolete.


texts: How to delete duplicate rows in merge replication

texts:
Somehow two rows in a table that is party to merge replication are identical, including their rowguid. I have no idea how this happened, it is only with two rows in my entire database (which is used in production).

No matter how I try to delete them, I get the following error:

  Msg 2601, Level 14, State 1, Procedure MSmerge_del_12B5E838BB91458D81AD66DD7EB5ABDC, Line 46
  Cannot insert duplicate key row in object 'dbo.MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'. The duplicate key value is (7094001, df2e61b2-3d8e-e511-84bb-00155d00c1da).
  The statement has been terminated.

This is somewhat expected, but I have no idea how to get around it. I'd very much like to avoid dropping anything big (i.e. replication, tables, etc.). This is an isolated case and I don't expect it to happen again so I don't mind doing a manual work-around.

Can anyone provide some insight into this?

Thanks!


texts: How do I do a JOIN in ANSI SQL 89 syntax?

texts:
In Oracle, I use

SELECT * FROM table1...JOIN...

where the dots represent either the type of join or the condition on which to be joined.

Is this ANSI 89 syntax? If not, then how would I perform a join if I were using ANSI 89?


texts: Can we update multiple record with join of two query

texts:
Can we run Update query to update multiple records with join ?
Below query is working fine when we running with one employee number -

update java set JAVA_MECHANISM='RECONCILIATION'
where usr_key =(select usr_key from usr
where usr_emp_no='******' and usr_status='Active')
and JAVA_MECHANISM= 'ACCESS';

Now requirement is want to run this query with all users who exist with where filter of 2 tables where usr.usr_status='Active' and java.JAVA_MECHANISM= 'ACCESS';  i.e.

update oiu set OIU_PROV_MECHANISM='RECONCILIATION',
POL_KEY=null, OIU_POLICY_BASED=null,
OIU_POLICY_REVOKE=null
where usr_key IN(select usr_key from usr where usr_status='Active'
and usr_udf_isexecutiveuser='1')
and OIU_PROV_MECHANISM = 'AP HARVESTED';

it’s taking too much time more than 10 mins but not completed as IN takes much time so any idea how we can get with oracle query ???


texts: PostgreSQL merging identical tables from separate databases into single database

texts:
I've started working on a project where the application data is split across multiple databases that all have the same schema (basically there's a database per "organisation").

After some discussion it's been decided that this isn't the most efficient way for us to store data and would like to merge all of these separate databases into one single database.

I was wondering what would be the best approach. I'm currently thinking about doing something like the following:

Dump the schema from one of the databases and restore it to the new primary database:

pg_dump [OLD_DATABASE] --schema-only > dbschema.sql

psql -d [NEW_DATABASE -f dbschema.sql
Dump the data from all of the old databases disabling triggers, and insert format:

pg_dump [OLD_DATABASES] --disable-triggers --data-only --inserts > old_db_data.sql
Restore all of the dumped data from database files:

psql [NEW_DATABASE] < old_db_data.sql

Is this the right way to do this kind of merge or am I completely off the mark? Additionally will this handle foreign key dependencies correctly?


texts:
I would like to get the filepath for my extended event event_file and put it inside a variable but I'm stuck.

Here's what I have:

DECLARE @ExpensiveXML XML

SET @ExpensiveXML = (SELECT CAST(xet.target_data AS xml) AS 'XML'
                    FROM sys.dm_xe_session_targets AS xet
                    JOIN sys.dm_xe_sessions AS xe
                    ON (xe.address = xet.event_session_address)
                    WHERE xe.name = '<my extended event>');

SELECT @ExpensiveXML.query('/EventFileTarget/File')

and it returns:

<File name="MyFilePath.xel" />

I would like to only return MyFilePath.xel without the < File name=.../ > around it.


texts: MySQL how do I priorities single column by multiple keywords

texts:
I have a column of product title, I have to order by result by max word's matched from entered string
Select Id, tittle, description,.....,
Case
When a.title REGEXP [[:<:]]Men's shoes[[:>:]] Then 0
When a.title REGEXP [[:<:]]Men's[[:>:]] Then 1
When a.title REGEXP [[:<:]]shoes[[:>:]] Then 2 END AS priority
From products
.
.
.

Order by priority ASC

It's work fine, when string come in order, but I want order by max matched word count,
or assigned first priority to that record which have max word matches


texts: Trigger doesn't run when value of a column changes from null to 1

texts:
I have this trigger:

delimiter $$

create trigger tr
after update on t1
for each row
begin

  if new.col1 !=old.col1
    then update t2 set col2 =1 where t2.col3=t1.col3;
  end if;

end

$$

This trigger works when col1's value changes from 0 to 1 but
when its changes from null to 1, the trigger doesn't make any changes in t2.

I just wanted to know why it is so. I know null is different, but I'm just comparing the values.

I have solved my problem by doing new.col1 =1.


texts:
I am new to database design and have started developing my first schema.

Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular use case could be that a user wants to add an additional field to their profile e.g. favourite color.

I have read a few articles that say the way to capture this use case is to include an 'other' field to the database. This field then maybe contains a json string of additional fields.

Is this method the best practice way to add additional fields to a table? It seems somewhat hacky to me. The obvious con is that the additional fields will not be as accessible when the database runs a query.

Note: I can see that there may be two different scenarios here. One where users have a personalised list of fields (in which case i think the method above will be the only way). Another where over time I realise all users would benefit from having an additional field in their profile. Comments on both scenarios would be appreciated.


texts: count(id) on Char(0) column does not work!

texts:
Possible Duplicate:
  What is the difference between select count(*) and select count(any_non_null_column)?

I have a column with the type of char(0).
some of the rows are NULL and some of them are empty('').
The question is that when I run the following query, the result will be 0.
SELECT count(id)
FROM test
WHERE id IS NULL

but when I run the following query, the result is correct:
SELECT count(*)
FROM test
WHERE id IS NULL

I have one column,why is that the case?


texts: Update audit database name in triggers and views of main database?

texts:
I have a a system with two databases, main database and audit database. A lot of the triggers and table views in the main database and audit database are referencing from one database to the other. No I needed to change both databases names but unfortunately they failed to work because they still have the old names in the code.

Is there a code to search and replace the old name used for referencing or in dependence?

Thank you,


texts: Entries count by date is giving wrong results

texts:
I am getting some strange results with below queries:

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2016-12-30'
and  `StockUpdated` <= '2016-12-31'

The result is : 24446

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2016-12-30'

The result is : 33098

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2016-12-30'
and `StockUpdated` <= '2017-1-1'

The result is : 33098

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2017-1-1'

The result is : 0

My question is why the below query is not perfect?

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2016-12-30'
and  `StockUpdated` <= '2016-12-31'


texts: db2 cannot execute C-function for Reason code 4

texts:
I've implemented a function as C code for db2. The library name is called libmyfoo.so and is stored id /usr/local/lib as declared in the External declaration. When I try to execute this function I get this error back.

SQL0444N  Routine "*HOT_IMPL" (specific name "SQL131129160401700") is
implemented with code in library or path
"...local/lib/limyfoo.so", function "*" which cannot be
accessed.  Reason code: "4".  SQLSTATE=42724

Essentially it is saying that it cannot reach that function but that's not true.
I've also setup the LD_LIBRARY_PATH for the db2inst1  user pointing to that folder.

On top of that I've also called the following that shuld reinforce the research path.

db2set DB2LIBPATH='/usr/local/lib:/home/db2inst1/sqllib/lib64:
/home/db2inst1 /sqllib/lib32'
db2set DB2ENVLIST='LD_LIBRARY_PATH'

What can I look to solve this error? db2diag doesn't change when I call the function so it is not helpful. How at least to debug it?

I am running db2 V9.7 on Linux Centos 6.4.

Below pieces of SQL function and C function.

create or replace function MyFoo()
  returns  table(
    a   integer,
    b   double ,
  )
  external name '/usr/local/lib/libmyfoo.so!MyFoo'
  language c
  parameter style sql
  no sql
  not fenced threadsafe
  returns null on null input
  not deterministic
  no external action
  scratchpad
  no final call
  disallow parallel
  no dbinfo
  cardinality 1000
;

 #if defined (_MSC_VER)
 extern "C" __declspec(dllexport) void SQL_API_FN MyFoo(
 #else
 extern "C" void SQL_API_FN MyFoo(
 #endi

     SQLUDF_INTEGER *a,
     SQLUDF_DOUBLE  *b,

     SQLUDF_CHAR    *,
     SQLUDF_NULLIND *i_ix,
     SQLUDF_NULLIND *i_ixp
     SQLUDF_STG_SCRATCH)
 {
   // Grab the snapshot on the first call.
   if (SQLUDF_CALLT == SQLUDF_TF_OPEN) {

I have just increased the log error to 4 and this is what I have in the log (finally). It appears 3 times.

 2013-12-04-17.20.12.629051+000 I29606333E883       LEVEL: Info
 PID     : 29170                TID  : 139915404764928PROC : db2sysc
 INSTANCE: db2inst1             NODE : 000          DB   : XTI
 APPHDL  : 0-7                  APPID: *LOCAL.db2inst1.131204171845
 AUTHID  : DB2INST1
 EDUID   : 21                   EDUNAME: db2agent (XTI)
 FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
  sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -444   sqlerrml: 70
  sqlerrmc: *HOT_IMPL SQL131202104044600 ...local/lib/libmyfoo.so * 4
  sqlerrp : SQLERLIB
  sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000
            (4) 0x00000000      (5) 0x00000000      (6) 0x00000000
  sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
            (7)      (8)      (9)      (10)        (11)
  sqlstate: 42724


texts: Advice on indexing a search table, should I or shouldn't I?

texts:
I'm making a search page on my website and I have 3 different areas that need to be searchable.

Should I make an index table for each using MyISAM engine. Or should I just put the extra data inside of the previous tables using InnoDB?

MyISAM (separated index table)

benefits

faster loading/search

cons

need to use CRON jobs to update it daily, same data in 2 places

InnoDB (all data in same table)

benefits

data all in one place, easier to maintain, instant updating

cons

possibly slower loading times

Does anybody have any advice on this?


texts:
I have a question regarding Hierarchical data.

Our app shows posts, that are structured inside parents. Here's how it goes:

prof->sprof->subject->post

(Each prof can contain multiple sprofs, and each sprof can contain multiple subjects, and each subject contains multiple posts)

Right now, we've set up a temporary MySQL Database that looks like this:

There are 4 tables: profs, sprofs, subjects and posts. Inside the table profs you have a row for each prof - and each prof has it's unique ID.

Inside the table sprofs, each sprof has it's own unique ID, but It also has a field for the ID of its parent. For example, if there's a prof with the ID 1, a sprof might have a parent_id of 1 (to indicate that its prof (its parent) is prof number 1).

And so on - each subject has a field for its sprof's ID, and each post has a field for its subject's ID.

So for example, if we want to get all the posts under the subject number 2, we do:

SELECT * FROM 'posts' WHERE 'subject_id'=2

A friend told me, that MongoDB can have structured data, so for example I could put a "table" of "posts" under a "row" inside the "table" of "subjects".

Is it true? Will it help? Will it be faster than using MySQL?

I'm not familliar with MongoDB at all, but I'd like to learn that anyway.


texts: After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB

texts:
I have a table: OrderDetails.

For 5 years, only the odd Deadlock has occured.
If it happened, it happened on indexes which was on OrderDetails table
Now all of a sudden, deadlocks are everywhere - happens very minute.
We have not made any changes to the application or database - the indexes rebuild every week.

The problem is that the table OrderDetails gets locked on occasion now and all hell breaks loose

Could someone please help and see if they spot anything from the Logs:
How can I get rid of the objectLock on the whole OrderDetails Table?
Why would the table lock -in the past it was only the Indexes that caused deadlocks.
Does this mean that the Indexes failed to find the appropriate records and now the whole table is locked?

It is an Update and a Select that are deadlocking here, and the rows that are locking are not related - they don't have the same Reference number or OrderID

 spid18s,Unknown,waiter id=process91bb58 mode=X requestType=convert
 spid18s,Unknown,waiter id=process9a9018 mode=S requestType=convert
 spid18s,Unknown,waiter-list
 spid18s,Unknown,owner id=process9a9018 mode=IS
 spid18s,Unknown,owner id=process91bb58 mode=IX
 spid18s,Unknown,owner-list
 spid18s,Unknown,objectlock lockPartition=0 objid=1194487334 subresource=FULL dbid=5 objectname=CompZ.dbo.OrderDetails id=lock32163640 mode=IX associatedObjectId=1194487334
 spid18s,Unknown,resource-list
 spid18s,Unknown,Proc [Database Id = 5 Object Id = 711009614]
 spid18s,Unknown,inputbuf
 spid18s,Unknown,OrderNoRef = @OrderNoRef and ClientID = @ClientID and  OrderNo <> @OrderNo
 spid18s,Unknown,where
 spid18s,Unknown,select  Distinct OrderNoRef from OrderDetails
 spid18s,Unknown,frame procname=CompZ.dbo.SP_Duplicate_Ref line=12 stmtstart=530 sqlhandle=0x030005004e25612a5f3d2a0194a100000100000000000000
 spid18s,Unknown,executionStack
 spid18s,Unknown,process id=process9a9018 taskpriority=0 logused=0 waitresource=OBJECT: 5:1194487334:0  waittime=8640 ownerId=17099323 transactionname=SELECT lasttranstarted=2013-06-11T14:34:34.230 XDES=0x2567fb58 lockMode=S schedulerid=2 kpid=15548 status=suspended spid=76 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2013-06-11T14:34:26.570 lastbatchcompleted=2013-06-11T14:34:26.570 clientapp=.Net SqlClient Data Provider hostname=WEBSVR hostpid=16664 loginname=*** isolationlevel=read committed (2) xactid=17099323 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
 spid18s,Unknown,Proc [Database Id = 5 Object Id = 562101043]
 spid18s,Unknown,inputbuf
 spid18s,Unknown,Update OrderDetails set OrderState = 15 where OrderNo = @OrderNo And OrderState in (1,2,3)
 spid18s,Unknown,frame procname=CompZ.dbo.SP_Insert_OrderDet line=59 stmtstart=1726 stmtend=1890 sqlhandle=0x0300050033fb8021a4e1360011a100000100000000000000
 spid18s,Unknown,executionStack
 spid18s,Unknown,process id=process91bb58 taskpriority=0 logused=0 waitresource=OBJECT: 5:1194487334:0  waittime=8609 ownerId=17099322 transactionname=UPDATE lasttranstarted=2013-06-11T14:34:34.230 XDES=0x3a2844e0 lockMode=X schedulerid=1 kpid=23896 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-06-11T14:34:25.347 lastbatchcompleted=2013-06-11T14:34:25.327 clientapp=.Net SqlClient Data Provider hostname=WEBSVR hostpid=16664 loginname=*** isolationlevel=read committed (2) xactid=17099322 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
 spid18s,Unknown,process-list
 spid18s,Unknown,deadlock victim=process9a9018
 spid18s,Unknown,deadlock-list

One of the Indexes had the page and row locks removed (was not documented so I did not know of this - I apologize for stating that nothing changed in the title)- is it correct to say that if both the row and page locks for a Index is disabled that it would then lock the table which uses that index? (Yes, if row and page locks are turned off, then only the table lock is available.)


texts: Structure database(s) dealing with different time points

texts:
I have few data sets that give a score for a specific account at a specific point in time. So, account A might have a different score each quarter going back 5 years so. There aren't a ton of data points per customer and we're only dealing with tens of thousands of accounts at the most.

I'm new to tying databases together, so I'm curious about the best way to structure this do I need multiple database or can this all be in one that I can easily query to get all the values over time for a specific account? Seems like this would be difficult though.

Any expertise you can lend would be helpful.


texts: SSMS Disk usage reports reports no data file for online database

texts:
We have a rather large database running on SQL Server 2012. The data file is now 2 118 256 MB. For a couple of weeks now, whenever I run the SSMS report Disk Usage, it says at the very bottom that "There is no data file for MyDatabase database."

Anyone seen this before? The database is up and running. Is there a size constraint on what this report will show?


texts:
I'm currently working with prototyping some stuff with a dump of a MySQL (5.7) database where much of the data is time dependent. Most of the queries I write use the NOW() function and the results change over time as we get further away from the time the dump was taken.

Is there a way to "lock the database in time" to the time of the dump or is there some other way to deal with this sort of situation, other than replacing all references to NOW() with a hard coded time stamp?


texts: repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

texts:
Working through a database corruption issue. I am working on a copy in a development environment. Some were corrupt indexes in which case I just dropped and recreated it. These however are data corruption issues that are of concern.

When I run:

DBCC CHECKDB ('DATABASE', REPAIR_ALLOW_DATA_LOSS);

...it says:

  the error has been repaired

Does that actually mean it fixed the problem or that it deleted the page which was the "repair" and data was lost?

  Object ID 1899231358, index ID 0, partition ID 124468026277888, alloc unit ID 124468026277888 (type In-row data):  Page (1:10429642) could not be processed.  See other errors for details.
  The error has been repaired.

  Table error: Object ID 1518653945, index ID 1, partition ID 72057594166444032, alloc unit ID 71875645566156800 (type LOB data).
  The off-row data node at page (1:10429980), slot 0, text ID 954358824960 is referenced by page (1:6660634), slot 10, but was not seen in the scan.
  The error has been repaired.


texts: Two diferent time mysql databases with same uniqueid autoincrement values need to be merged

texts:
I am a beginner at SQL, so I will try to ask for help.
As I have a database where is primary key where values are incremented by +1.
And I made this database backup, and have another database same as that one.
Where unique id also auto increments. But now I need to insert the old database into an old one.
The only way to separate from old to new is the DateTime colon where is the date of each database value.


texts: Query a name in large database, optimizations?

texts:
if you would like to query a name in a very large database, are there some ways to optimize this query? If 1 million names are in the database, and the name has to stay as it was registered (and not with a number for example , myName_108008080 ). I am wondering that in the context of games, when someone is looking for someone else in the database just with his name, the query is quite fast even when the database contains many entries.

Thanks


texts: pt-table-checksum toolkit for few databases from file

texts:
I've got a question. I'm doing a simple script right now and it isn't working like I want.

    #!/bin/bash

    cat /etc/backup.conf | egrep -v "(^#.*|^$|^--.*)" > /tmp/databases

    while read line; do
    pt-table-checksum [options] --databases $line --quiet
    done < /tmp/databases

    rm /tmp/databases

As you see I put names of databases to file /tmp/databases. Then I want to use pt-table-checksum for every database in that file line by line. This script works but only for first database name in file. Then it stops.

Any ideas?


texts:
In my web application (java+mySQL) I have the following server side (pseudo)code:

select count(*) c form table where condition;
if c>0 then update...
else insert...

In some situations (heavy load, multiple click, slow connections) it seems that different requests execute the select at once, generating thus multiple records instead of only one.

I'm looking for the best and safest way to avoid this.
It will be enough to put the whole block in a transaction, and which level, serializable?
Should I be careful with locking conditions, considering that other parts of the application could write/read on the same table for other reasons?

Other solutions?

PS.

INSERT ... ON DUPLICATE KEY UPDATE is not an option as I don't have an usable unique key.

As requested

CREATE TABLE start (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
startDateTime datetime DEFAULT NULL,
status varchar(10),
PRIMARY KEY (id))

And the code should look like

query=select count(*) c, id from start where date(StartDateTime)=curdate()
if c>0 then update start set code="updated" where id=query.id
else insert start (startDate,code) values (curtime(),"start")

This code is heavily simplified. I could solve it with insert...on duplicate key if I could define a primary key as the function date(startDateTime). But mysql 5.6 doesn't support it.

I could also add a column with date(startDateTime), but this is not easy done code-side.


texts: Explicit transaction left open after try/catch

texts:
I recently wrote a T-SQL script to perform some updates and inserts on 3 different tables. I wanted it done in a single transaction, so I read up on the Microsoft documentation on how to use explicit transactions with try/catch.

According to the documentation, it can be done like this:

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

So I implemented this pattern and put all my updates and inserts into the TRY clause. Problem is, the code managed to leave an open transaction after the query finished, and I do not see how this would happen. Regardless of what work is done inside the TRY-clause, what possible scenarios could cause a query like this to leave a transaction open?


texts: most efficient way to deal with real time gps data. S3 vs dynamoDB vs postgres

texts:
I have an application that is very write heavy. It saves GPS data every second. The way I handle it at the moment is to store the data locally and upload it to s3 every 5 minutes. The database (dynamoDB) then only contains the metadata about the gps file.

Now I would like to get all the gps data into the db rather than just the metadata. I thought about a aws lambda function which automatically reads the gps file and saves the entries to the db, but this has one big problem.

DynamoDB is priced by write capacity (i.e. about 5$ per month for every 10 writes/s). So I either have to pay a lot of money to get the necessary capacity (5min = 300 entries/writes, multiplied by the amount of simultaneously uploaded files) or throttle the db writes to slowly process all the entries (this is not really an option since I need the file contents to get in the DB close to realtime).

Therefore I consider switching to AWS RDS postgresql since it does not charge per request and also allows multi item inserts. Does anybody have experience regarding the performance on a micro instance? Can it handle potentially hundreds of incoming multi row insert statements (about 300-1000 items each).

Another short question. I use csv to store the gps data since it uses much less space than json and therefore saves bandwith. What does the result of a select statement look like?
Is it something like:

firstname,lastname
steve,jobs
michael,jackson

or rather:

firstname: steve, lastname: jobs
firstname: michael, lastname: jackson


texts: Any issue in referencing a unique index from a foreign key table?

texts:
Is there any concern in referencing a unique index from a foreign key table?

Using SQL Server 2008 R2


texts:
On SQL Server 2012, we have been using the [model] DB so that we can easily create project DBs with all the objects (schemas, tables, stored procedures, user-defined types) already set up with a CREATE DATABASE statement.

However, if we want to clear all of this out and start over, is there an easy way to do this? Naturally we can't DROP it.


texts: How do I restrict PostGIS database with user specific access?

texts:
I am using open source tools and working on QGIS 2.8 and Postgres/PostGIS 9.3 .

Working on urban application so we have single database and many users .I want to create department wise user group so that that group can not access other tables in database.

I want to update data to want to share data to them.

e.g. electric department should use only view/edit their data and no other department person should edit their data.

Tried following :-

1.Searched on google and PostgreSQL help as well created few users and their roles but when I log in using postgis by 'user1' credentials in QGIS I can see all my data.

2.Going through MANAGING USERS AND PERMISSIONS IN POSTGRESQL AND POSTGIS

thanks..


texts: How to combine two select statements result column wise?

texts:
I have two select queries and i want to combine result of both column wise.I have use union between two select queries but it give me just one query result

I want expected result like this

My SQL QUERY

    SELECT TC.DESCRIPTION, count(TE.CategoryID) AS COUNT
   FROM tblCategory TC
    LEFT OUTER JOIN tblEvent TE
    on TE.CategoryID=TC.NO
    AND TE.AssetID IN (
                        SELECT ASSET_NO
                        FROM tblAsset
                        WHERE EQUIPMENT_ID=1
                        )
  GROUP BY  TE.CategoryID,TC.DESCRIPTION

  UNION
  SELECT TC.DESCRIPTION as desc2, count(TE.CategoryID) AS COUNT2
  FROM tblCategory TC
  LEFT OUTER JOIN tblEvent TE
    on TE.CategoryID=TC.NO
    AND TE.AssetID IN (
                        SELECT ASSET_NO
                        FROM tblAsset
                        WHERE EQUIPMENT_ID=2
                        )
  GROUP BY  TE.CategoryID,TC.DESCRIPTION


texts: Cant connect to local teradata database in VMware image

texts:
Login credentials for VM

linux: root/root
database: dbc/dbc

The image`ve been downloaded from here. (this version TDExpress15.00.02.08_Sles11_40GB.7z)

I successfully launched VM image, but failed to connect to database. I started Teradata studio and entered dbc/dbc  as login/pass – and he couldn’t connect(ping problems).

How can I check the existence of teradata? What am I doing wrong? Is where any user guide?

UPD1  I`m trying to connect within the VM. Result of next command:

  pdestate -a

pde state is RUN/STARTED.
DBS state is 0/-1: DBS is not running

I tried such command, but I think it doesn't work properly:

  /etc/init.d/tpa start

PDE (PdeMain) already running: use 'tparest -f force'

  tparest -f force

you are about to restart the database on the system 'SLES11SP1'
do u wish to continue?

So I pressed Y, then I repeated this steps several times, so DBS state changed to 5. And I connected successfully))


texts: Unique pair checking query improving in pivot table

texts:
First of all, I have a solution, but I do not know that query can be transformed to uses join or to more effective query. I just want to improve my skill.

I have seen an example online.

So I have a pivot table which contains two column: profile_id, followed_id, each column a reference to the Profile.id column, this is a friend list.

I created a query to check for self-loops like:

profile_id | followed_id
         3 | 5
         5 | 3

As in the link this is a "duplication" because it represents the same relation between the profiles.

I created a query to check for duplication, this is a challenge for me. Yes, I added unique constraints to the table, but I was curious about I can write a checker query or not.

This is what I figured out:

SELECT *
  FROM
    tbl_profile_follow A,
    tbl_profile_follow B
  WHERE
    (B.profile_id = A.profile_id AND B.followed_id = A.followed_id)
    AND
    (B.profile_id = A.followed_id AND B.followed_id = A.profile_id)

I have thinking about there are any more effective query for this problem or not. For me it seems I cannot transform this query to join because my pivot table does not use primary key.
However if I use primary key in this pivot table and try to user join will I get better performance?

Of course I apply postgres specific query to learn postgresql :)

Thanks for the answers


texts: Script to find unused indexes over a period of time doesn't work in Oracle 11g

texts:
The statspack_unused_indexes.sql script described in this page would be incredibly useful to me, but it has been coded for an earlier version of Oracle. When I try to run it on 11g, I get the following error:

SQL> @statspack_unused_indexes.sql
Enter value for begin_snap: 10000
Enter value for end_snap: 10600
old  48:    sn.snap_id between &begin_snap and &end_snap
new  48:    sn.snap_id between 10000 and 10600
   stats$sql_plan_usage spu
   *
ERROR at line 28:
ORA-00980: synonym translation is no longer valid

Is there some easy patch to make that work on 11g? Here's the code (copied from the referenced page):

ttitle "Unused Indexes by Time Period"

col owner heading "Index Owner" format a30
col index_name heading "Index Name" format a30

set linesize 95 trimspool on pagesize 80

select *
from
   (select
      owner,
      index_name
   from
      dba_indexes di
   where
      di.index_type != 'LOB'
   and
      owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
minus
select
   index_owner owner,
   index_name
from
   dba_constraints dc
where
   index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
minus
select
   p.object_owner owner,
   p.object_name  index_name
from
   stats$snapshot       sn,
   stats$sql_plan       p,
   stats$sql_summary    st,
   stats$sql_plan_usage spu
where
   st.sql_id = spu.sql_id
and
   spu.plan_hash_value = p.plan_hash_value
and
   st.hash_value = p.plan_hash_value
and
   sn.snap_id = st.snap_id
and
   sn.dbid = st.dbid
and
   sn.instance_number = st.instance_number
and
   sn.snap_id = spu.snap_id
and
   sn.dbid = spu.snap_id
and
   sn.instance_number = spu.instance_number
and
   sn.snap_id between &begin_snap and &end_snap
and
   p.object_type = 'INDEX'
)
where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
order by 1, 2
/


texts: It seems to me that SQL THROW is missing a crucial feature that RAISERROR has, and that is the abilty to use WITH LOG so that an Alert can be fired when the error number is logged in the Application log. Is there a simple way to do this that is escaping me or do I still have to rely on the more cumbersome RAISERROR and sp_addmessage SP? I would like to be able to notify an Operator through an Alert using THROW in a CATCH block.


texts: How to install and configure Postgres-XC in windows?

texts:
Can anybody suggest me any resources of the complete installation procedure of postgres-XC in windows. I've installed postgresql-9.2 in my windows and can  use it. Now I need to know the installation procedure of postgre-XC and the way to do clustering and the whole distribution and replication process using postgres-XC. I've googled and found few but all of them are for linux. But I need this for windows.

FYI:

http://manojadinesh.blogspot.com/2012/08/postgres-xc-setup.html
http://alexalexander.blogspot.com/2013/01/postgres-xc-explained.html

Thanks


texts:
I want to fetch a Package DDL in Oracle 8i.

8i doesn't support DBMS_METADATA.get_ddl.

Can it be done using export?


texts:
SETUP

2 Amazon EC2 SQL 2014 servers in different availability regions.
Synchronous mirroring
S3 for archiving full backups & logs at night
Right now for simplicity, nightly full compressed backups
Tran Logs every 15 mins (would do more if can reduce the runtime)
Ola Hallengren backups running on both principal and mirror servers

SLA

My SLA terms to meet don't have heavy restrictions at this point so (odd I know!):

"Opps Deleted Something" restores would be a nice to have, but I don't a hard delivery requirement on this right now. I do log backups every 15 mins right now.
High-Availability: not fully required, downtime of an hour would be acceptable if needed
Point in time recovery: I'm able to provide due to the transaction log backups every 15 mins, however 2 hours would be acceptable for now.

Network Bottleneck

It looks like one of the biggest bottlenecks is the approx 300mb network pipe that seems to get overloaded by the need to synchronously mirror to another EC2 instance. I could maintain a lot more databases on the same instance, reducing costs likely if I removed mirroring.

Mirroring or Alternative Approach For performance with reasonable availability

I'm looking for the best value while still delivering a reasonable uptime. Since I'm on SQL standard, this means that asynchronous mirroring is not an option (we are running sql 2014).

As I've evaluated options I'd appreciate another perspective on basic disaster recovery. The overhead of mirroring seems to be a big bottleneck, but if I remove it then I'm concerned about how to provide better availability. Ideally, I'd run mirroring asynchronously, but we'd need to move backwards in sql server editions, and not sure if that would be the best approach

always on high availability groups

Not proceeding with this due to the increased complexity and licensing cost at this time. Open to exploring in future, but at this time looking to avoid Enterprise licensing cost.


texts:
I am looking for the actions one must take to cause Threshold Alerts to occur for a single mirrored database on an instance of SQL Server 2008 containing multiple mirrored databases.

FYI: This is already posted on ServerFault.com and submitted as a bug on Microsoft Connect.


texts: DB2 Transaction Logs - can they be COPY-PASTE on another Disaster Recovery Server?

texts:
We have Server1 with Online Backup1, Archive Logs 1,2,3 for example and Transaction Logs from  1 to 10.
We are interested in next scenario:
If Server1 fails completely and we want to move everything on second server I suppose it is not the problem to move and restore Backup1 and Archive logs 1,2,3 on Server2.
But can Transaction Logs (their snapshot in some specific time) be also copied to the Server2? Because Transaction Logs are probably the most problematic part for this Disaster Recovery Scenario. Can there be some problem with the fact that transaction logs are written and populated all the time?

ADDITION: DISK option is used when configuring Archive Mode


texts: Database design: self referencing vs separate tables

texts:
So, i have 1 table which contain payments with different type

id| reference_id | amount | type
1 | null         | 200    | debit
2 | 1            | 25     | refund
3 | 1            | 175    | refund
4 | null         | 0      | regist
5 | 4            | 100    | debit

as you can see, this is one entity named payment, each row MAY has relation to another row,

There is another way to represent this data by separating each type to their own tables. I will need debit_payment, refund_payment, regist_payment, and so on.

Which one is the best for this ? especially when we need to show list of the payment, and we also need to get their relation.


texts: Performance Monitoring for Prepared SQL?

texts:
My company is looking into changing to full use of prepared SQL across the board for some applications. For a few days, I've been trying to answer the question of "Do we have a way to monitor for performance" such as seeing a SQL transaction having high duration/CPU/IO actions and seeing what it was doing to produce that high load. Much of what I've come across has been concerns that this is unnecessary/obsolete in Microsoft SQL Server. I've raised concerns about this but in the mean time I'm still trying to come up with a way to track activity.

The best I've identified is that we can have our standard performance monitoring that we've always had, but we will also need a sort of "magic decoder ring" trace/XE session. This would have to track Prepare/Unprepare SQL actions to get the handle, and the associated statement completed messages to get what the associated query is for each SPID. Then if we encounter in our performance monitoring a transaction saying something like "sp_execute 32 etc etc..." we would look at the SPID requesting this action, take that handle # 32 (or whatever it is) and look at our magic decoder ring to figure out what the parameterized SQL statement is that is being run for analysis.

The answer we would be using currently needs to support SQL2008R2 and up (we have many hundreds of this version deployed at present as we are starting to move to next gen), which is why I mention SQL trace as we still utilize this over XE for many deployments due to the limitations of XE before SQL2012.

We will still be looking at whether or not prepared SQL is a good idea for us, as some new information coming up in research suggests it may not be. I'm simply asking if there is a clean way to directly monitor actions done via prepared SQL without such a cumbersome "magic decoder ring" method for keeping track of what the prepared SQL is.


texts: Is there a reason to store currency amounts as fractions instead of decimals?

texts:
I've been reviewing GnuCash's DB design and I found that they are storing amounts as two 64-bit integers, the numerator and denominator of a fraction.

Is there some advantage from a DB perspective to storing financial data this way?  I always learned (in MS SQL Server) to use decimal(18,4) for currencies values.


texts: Should I install all cumulative updates/service packs when doing a clean install of SQL Server 2016? 2019?

texts:
Going to set up a new database instance from scratch. Debating between SQL Server 2016 and 2019.

I've never installed a service pack or cumulative update on previous instances, mostly because everything was working fine.

Since this will be a clean install, should I patch right away before it goes into production? Is there any downside?


texts: Index for the items inside a jsonb array in Postgres

texts:
I've got a table which looks like this:
create table data (
  id serial primary key,
  report jsonb
);

with the data like this:
insert into data (id, report) values
(1, '[{"item_id": 1, "item_data": "[1,1] data"}, {"item_id": 2, "item_data": "[1,2]"}]'::jsonb),
(2, '[{"item_id": 1, "item_data": "[2,1] data"}, {"item_id": 2, "item_data": "[2,2] data"}]'::jsonb);

The array is actually nested to some json objects, I just simplified the stuff.
And I can fetch the data like this:
explain analyze
with data_row as (
  select * from data where id = 3
)
select id, item_id, item_data
from data_row, jsonb_to_recordset(data_row.report) as items(item_id integer, item_data text)
where item_id = 999;

+----------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                |
|----------------------------------------------------------------------------------------------------------------------------------|
| Nested Loop  (cost=1.24..2.52 rows=1 width=40) (actual time=257.374..304.255 rows=1 loops=1)                                     |
|   Buffers: shared hit=487, temp read=2246 written=2245                                                                           |
|   CTE data_row                                                                                |
|     ->  Seq Scan on data  (cost=0.00..1.24 rows=1 width=36) (actual time=0.008..0.011 rows=1 loops=1)                            |
|           Filter: (id = 3)                                                                                |
|           Rows Removed by Filter: 18                                                                                |
|           Buffers: shared hit=1                                                                                |
|   ->  CTE Scan on data_row  (cost=0.00..0.02 rows=1 width=36) (actual time=0.010..0.014 rows=1 loops=1)                          |
|         Buffers: shared hit=1                                                                                |
|   ->  Function Scan on jsonb_to_recordset items  (cost=0.00..1.25 rows=1 width=36) (actual time=257.362..304.238 rows=1 loops=1) |
|         Filter: (item_id = 999)                                                                                |
|         Rows Removed by Filter: 499998                                                                                |
|         Buffers: shared hit=486, temp read=2246 written=2245                                                                     |
| Planning time: 0.144 ms                                                                                |
| Execution time: 306.647 ms                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------+

But it's too slow and simple gin index on jsonb field also doesn't help.
Of course, I can normalize the DB, but the question is "Is it possible to create any index to make the request above efficient(or other request doing the same thing?"


texts:
I want to use parameters in the below cte but I get the below error messages

Msg 102, Level 15, State 1, Procedure X82 7 Days/by Day, Line 5
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure X82 7 Days/by Day, Line 21
Must declare the scalar variable "@startDate".

Once I can get the perameters to work I will turn the static pivot into a dynamic one

    ALTER Procedure [dbo].[X82 7 Days/by Day]
            (
            @StartDate As datetime = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0)
            ,@EndDate As datetime = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0)
            )
    As
    Begin

    ;with cte as(

    select  [CAB Part No.]
        ,[RFT/Scrap/Rework]
        ,[Process No.]
        ,[Process]
        ,[Date & Time]
        ,[Quantity]
        ,cast([Date & Time] as date) UtcDay
    from    [SFDLOG]
    where   [Date & Time] between @startDate and @EndDate
    )
    select  [CAB Part No.]
        ,[RFT/Scrap/Rework]
        ,[Process No.]
        ,[Process]
        ,[Quantity]
        ,UtcDay
    into #TempDates
    from cte

    select *
    from
    (
    select   [Quantity]
        ,[utcday]
        ,[CAB Part No.]
        ,[Process No.]
        ,[Process]
        ,[RFT/Scrap/Rework]
    from #tempDates
     ) x
    pivot
    (
    count([Quantity])
    for [utcDay]
    In ([2015-02-01],[2015-02-02],[2015-02-03],[2015-02-04],[2015-02-05],[2015-02-06],[2015-02-07])
    )p

    Order By [CAB Part No.],[Process No.]

    drop table #tempdates

End


texts: PostgreSQL streaming replication lag for synchronous standbys

texts:
We use PostgreSQL 9.5 in a master + synchronous hot-standby setup, i.e., using synchronous_commit = on and using synchronous_standby_names='*'.

Consequently, the standby has sync_state = sync in the master's pg_stat_replication_table.

We also use postgres_exporter, which uses the following statement to extract the system's replication lag from the master's stats.

SELECT *, pg_current_xlog_location(),
       pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::float
FROM pg_stat_replication;

Question: Does the above call to pg_xlog_location_diff always return 0 in a synchronous setup, or are there any cases when there is a measurable replication lag?

To my understanding, if the above statement shows a lag, it means that the master has committed a transaction without waiting for the standby, which should never happen in a synchronous setup.

Note that we moreover use replication slots, have setup the cluster using repmgr, and do not override synchronous_commit locally for individual transactions.


texts: How to avoid conditional join in order to write a tsql view

texts:
I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables

Roles table

RoleName
---------
Admin
Viewer
User

Set table

Id      Name
---     ----
1       Employee Data
2       Help Desk
3       Comments
4       Interests

DefaultSet table

RoleName   SetId
--------   -----
Admin      1
Admin      2
User       1
User       3

CompanySet table

Company   RoleName   SetId
---------   --------   -----
Acme Inc    Admin      1
Acme Inc    Viewer     1
Bobs Paint  User       2
Dans Disks  Admin      2

This query works but I want to be able to restructure it in a way that it is parameterless('Acme Inc' condition is the param) and can be used in a view.

WITH T1 AS
(
    SELECT Company, RoleName, cs.SetId  FROM Roles r
    LEFT JOIN CompanySet cs on cs.RoleName = r.RoleName
    GROUP BY Company, r.RoleName, cs.SetId
),
T2 AS
(
    SELECT  RoleName, ds.SetId  FROM Roles r
        INNER JOIN DefaultSet ds on r.RoleName = ds.RoleName
        GROUP BY r.RoleName, ds.SetId

)
SELECT * FROM t2
    LEFT JOIN t1 ON t1.RoleName = t2.RoleName
                 AND t1.SetId = t2.SetId
                 AND T1.Company = 'Acme Inc'

Seems like maybe I have to cross join the company on the t2 table so I can match the condition against it. Let me know if there is a way to solve this.


texts: Searching for a close numeric match on spatial coordinates

texts:
I have a large Postgres table contain geographic positions (latitude and longitude) both fields are indexed, and both are defined as NUMERIC(9,6).
If I run a query looking for an exact position match, something like this:
WHERE latitude  = 1.234567890123456789
AND   longitude = 9.876543210987654321

Then get a very fast response, but I get very few results because the database is searching for a very precise match.
For my purposes, I'm looking for positions that match to within a few meters so a match to 4 or 5 decimal places should be fine. This gives me the results I'm looking for:
WHERE ABS(latitude  - 1.234567890123456789) < 0.0001
AND   ABS(longitude - 9.876543210987654321) < 0.0001

But NOT the performance (it can take 5 minutes to run, compared to a fraction of a second for the exact search)
Next I tried rounding the precision down:
WHERE ROUND( latitude, 4) = ROUND( 1.234567890123456789, 4)
AND   ROUND( longitude,4) = ROUND( 9.876543210987654321, 4)

Again, same problem. Got the results I wanted, but took far too long.
So, my question is how can I search for a close match between two numbers, without losing performance?
UPDATE - SOLVED:
As a couple of commenters have observed, using BETWEEN seems to work fine.


texts: mysql picasa like photo tags store and query

texts:
I want to make a photo website, allow users to upload photos and write 1-10 customs photo tags, like picasa. I use mysql.

Tags could be words or short sentence, but total length can not over 80 alphabets by each. Users can make the tags what they like.

I wanna to ask, what is the best way to design this kind of database? easy for store and query. BTW: Also need a easy query to see which tags are more popular(show 10 populars). Thanks.


texts: Ubuntu / MySQL - How to choose from the various downloads?

texts:
I have Ubuntu 12.04.2 LTS.  I would like install MySQL 5.5.29.

So I go to the MySQL 5.5 archive page
http://downloads.mysql.com/archives.php?p=mysql-5.5&o=other
and scroll down to section 5.5.29

Here I see the following options:

There appear to be duplicates here, so I hover the mouse over the links.

mysql-5.5.29-linux2.6-x86_64.tar.gz
mysql-5.5.29-linux2.6-i686.tar.gz
mysql-5.5.29-debian6.0-x86_64.deb
mysql-5.5.29-debian6.0-i686.deb
mysql-5.5.29.tar.gz
mysql-5.5.29.zip

My operating system, Ubuntu 12.04.2, is 64 bit, so that rules out #2 and #4.  I assume the .zip is for Windows, so that rules out #6.

That leaves #1, #3 & #5.

Please explain how I choose from these remaining options?
Perhaps Ubuntu is considered similar to Debian so thats what I should choose?
Or should I select the generic download?


texts: SQLAnywhere: Savepoint for ROLLBACK not found

texts:
What is the correct way on setting and rolling back to savepoint on SQLAnywhere. Having this snippet:

begin transaction;
SAVEPOINT spt_abc;
insert into eins (pl1) values (5);
SAVEPOINT spt_123;
UPDATE eins SET pl1 = 10 where pl1 = 5;
ROLLBACK TO SAVEPOINT spt_abc;
commit;

Getting this error message while running it in dbisql:

  Could not execute statement. Savepoint 'spt_abc' not found SQLCODE=-220, ODBC 3 State="HY000" Line 6, column 1 You can continue
  executing or stop.

  ROLLBACK TO SAVEPOINT spt_abc

the very same happens when trying to rollback to spt_123.


texts: Does number of rows affect Update Statistics performance if you are sampling with n rows?

texts:
For example, I have table A with 80 million rows and table B with 200 million rows.

Assume both tables have 30+ stat tables (all manually generated by SQL Server).
I randomly take the first 10 stat tables from each and run the two tasks separately:

Update Statistics A (stat1, stat2, ...) with sample 5000000 rows
Update Statistics B (stat1, stat2, ...) with sample 5000000 rows

Even though both are scanning 5000000 rows only, the second task is taking much longer to complete. Is this difference in performance solely from the difference in the contents of the tables, or does the number of rows in the table also play a part even if we are limiting the number of rows to scan?


texts: Can both primary and secondary servers be online simultaneously in Log Shipping?

texts: I am new to log shipping and I want to know can both primary and secondary servers be online at the same time like in replication, or only the primary one can be online while others get the log backups but are in a passive state?


texts: Environment Variable PATH exceeding recommended length

texts:
I'm installing Oracle 12c on Windows 10 Pro and receiving the error message:

  "PRVF-3919 : Failed to retrieve value of environment variable "PATH""

I've read through several similar questions and followed recommended procedures:

I'm executing the installation setup as Admin
I renamed Path to PATH
I counted the existing chars in Path (834, well under the 1023
limit)
I deleted unnecessary Path statements to reduce size
I deleted all entries in Path to start fresh.

All to no avail.  I'm still getting the same error message.  Does anyone have any other ideas or suggestions?

Thank you,
mfc


texts: MariaDB Rows matched: 15135 Changed: 0 Warnings: 0

texts:
So...

I find this to be strange:

UPDATE cFlights c
LEFT JOIN airports a on a.IATA = c.depCode
SET c.depId = a.id, c.depName = a.name
where c.depCode!='' and c.depName is null;

I know that table design is not ideal, but I am not sure why update does not work?

Output is:

  Query OK, 0 rows affected (1 min 28.94 sec) Rows matched: 15135
  Changed: 0  Warnings: 0


texts: Can an index on one table be used in the query for another table?

texts:
I am working in SQL Server (2014).

I have a table that contains an XML column. There are a well known set of queries against that column that I would like to materialize as persisted computed columns for querying purposes. However, I don't want to add those columns to the table for various reasons.

My question is, is there some way to create a second table that contains indexes/keys that will be used by the optimizer for the first table? Is this achievable with hints or some other mechanism?


texts: SQL Server: How does Uniquifier work with Replication?

texts:
In SQL Server, if I have a non-unique clustered index on a table, how does replication work for that table?

For example:

CREATE TABLE MyData (
    MyDataID uniqueidentifier not null,
    MyData varchar(max) not null,
    RecordDateTime smalldatetime not null,
    PRIMARY KEY NONCLUSTERED (MyDataID ASC)
)

CREATE CLUSTERED INDEX IX_MyData_RecordDateTime ON MyData (
    RecordDateTime ASC
)

(For purposes of the question, the table structure is more illustrative than exact)

I know that SQL Server will add a hidden 4-byte "uniquifier" to rows with duplicate RecordDateTime values, but in this case, how are the rows replicated?

Specifically, if there are two rows R1 and R2 on two publishers P1 and P2, if R1.RecordDateTime is unique on P1 and R2.RecordDateTime is unique on P2, but R1.RecordDateTime is equal to R2.RecordDateTime, how are the rows merged onto a shared subscriber S?


texts: Update statements with set transaction isolation level read uncommitted

texts:
If I have a stored procedure with set transaction isolation level read uncommitted, will it affect update statements?

I know that you should not use with (nolock) on update/delete statements, and this does pretty much the same thing but not sure if SQL ignores it on update statements in the procedure or if I should be careful not to use it if there are update statements.

EDIT:

Sorry for the confusion. I'm not trying to figure out what the effect of using this type of locking on manipulation statements would be or whether it's a good idea. In fact I do NOT want to use this kind of locking on manipulation statements, and so my question is whether putting "set transaction ..." at the top of my stored proc is ever going to be honored by update/delete statements or whether it will be ignored. My hope is that it is just ignored.

I am fully aware of the effects (with its pros and cons) it will have on select statements.


texts: Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement?

texts:
I have this table:

SELECT * FROM items

id | item      | position
---|-----------|----------
 1 | USB cable |        0
 2 | SD card   |        4
 3 | Mouse     |        2
 4 | Keyboard  |        0
 5 | Monitor   |        3

Sorting this table gives this result:

SELECT * FROM items ORDER BY position

id | item      | position
---|-----------|----------
 4 | Keyboard  |        0
 1 | USB cable |        0
 3 | Mouse     |        2
 5 | Monitor   |        3
 2 | SD card   |        4

Now I want to update the table and save the order in the position column:

SELECT * FROM items

id | item      | position
---|-----------|----------
 4 | Keyboard  |        1
 1 | USB cable |        2
 3 | Mouse     |        3
 5 | Monitor   |        4
 2 | SD card   |        5

Can this be done with a single query or do I have to manually loop over all rows and do a manual update?

In case the order is not fully defined (e.g. for USB cable and Keyboard above) I just arbitrarily decided the order.


texts: check key if exists in other table without fk constraint

texts:
Lets say I have:

table a (id_a int);
table b (id_b int, id_a int);

I want to check if b.id_a exists in "a" before a new row in "b" will be inserted. Normally I would just add a fk constraint on b.id_a. The problem is that I'm not allowed to add a pk or uq constraint to "a.id_a" to reference the fk on b.id_a.

Would be nice if somebody has a solution for me.

Edit:
Why not fk constraint - My model consists of an ESRI Arc SDE with spatial tables and simple postgres tables. Some pg tables reference to an sde uq identifier column. By default the SDE adds no pk to a spatial table in postgres (in sql server it does dont ask me why). However if a pk would be added by default by the SDE it wont help me at all, because i would have to change it to an other uq column, because the default pk column keys could change in the future (=WTF). ATM i just added or changed the pk of the SDE table to my uq identifier column, but as i had errors at first when restoring the db (now it works for the moment), ESRI support told me that my change or add of the pk column can cause these errors or maybe cause some in the future (because SDE wont know this pk). In my opinion it the pk should not matter (because SDE wont know and also wont use it) that's why i added it atm and it seems to work. But for the future, i want to be on the safe side and so i have to find an other possibility.

What i want: It would be nice to have the same behaviour like with a fk constraint - but i guess a similar behaviour wont be possible because that's what fks made for.

I have tried to write a before insert trigger but i think its the wrong approach as it seems that i always have to return the new.row and i don't want something to be inserted when the check for the value to be inserted don't exists.

I also could simply use no relationship or check on the fk but this in my opinion would be a high risk for having wrong values in the tbl.

Thanks for helping me...

Possible Solution:

I have tried to add a fk constraint which references on a column with uq index and it works (thx to ypercube). I have taken a look at the doc, but i cant find something about adding a fk constraint referencing on a column with just a uq index (no pk or uq constraint). At least i found a possible explanation in a tutorial which says

  A unique constraint is actually implemented as a unique index in PostgreSQL, just as it is in many databases.

but also

  This index wouldn’t be a good candidate for a foreign key, but it does illustrate how you can create a unique index that you can reference with a foreign key.

I just don't really get it why it shouldn't be a "good candidate" for a fk constraint, when either using a uq constraint or uq index on that column grants you unique values.

from docs pg uq constraint:

  Unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all the rows in the table.

from docs pg uq index:

  Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.


texts: Execution Plans Differ for the Same SQL Statement

texts:
As a background to this question the details can be found at this link.

In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the returned columns vary because the tables are different. The databases were moved to a SQL Server 2012 instance from 2008. On the 2012 instance, 1 query executes in under 3 seconds, and the other takes almost 2 hours to execute. The execution plans differ and are posted at the link.

However, I can take both queries and execute them on the SQL Server 2008 server and they both complete in under 3 seconds. Both execution plans on the 2008 server are identical to the 'Good' execution plan on the 2012 server.

I have a new statement that will work but the application that it's used in is a C# windows application using inline SQL. I would like to not break that pattern for this 1 statement. Can anyone shed any light on this problem?


texts: how to solve ORACLE AUDSYS.SYS space error

texts: I am having this problem "Warning: odbc_connect(): SQL error: [Oracle][ODBC][Ora]ORA-02002: error while writing to audit trail ORA-55917: Table flush I/O failed for log ID: 1 bucket ID: 0 ORA-01691: unable to extend lob segment AUDSYS.SYS_LOB0000081269C00014$$ by 128 in tablespace SYSAUX ORA-02002: error while writing to audit trail ORA-55917: Table flush I/O failed for log ID: 1 bucket ID: 0 ORA-01691: unable to extend lob segment AUDSYS.SYS_LOB0000081269C00014$$ by 128 in tablespace SYSAUX , SQL state S1000 in SQLConnect in..." is there any known solutions?


texts: Question about cost optimization formula for SELECT

texts:
I am a student in computer science and I have a question, which refers to cost optimization. I have to read the book (Fundamentals of Database Systems from Elmasry and Navathe) and try to present the topic to class. But I have some issues understanding one cost formula for B-tree-index:

  Using a secondary (B+-tree) index: [...]If the comparison condition is
  (> >=, <, <=) and half the file records are assumed to satisfy the condition, then(very roughly) half of the first level index blocks are
  accessed, plus half the file records via the index. The cost estimate
  for this case, approximately, is C=x+(b_l1/2)+(r/2).[...]

  Using Linear search: [...] C=b[...]

where

x = number of levels of multilevel index
r = number of records
b = number of blocks
b_li = number of first-level index blocks
C = cost function

I don't understand the formula for the secondary index. I think the formula should look like this:

  C=x+(b_l1/2)+(b/2)

Can somebody explain me, why the formula uses the number of entries instead of number of blocks?

Thank you very much


texts:
I want to do some cohort analysis of gamer activity in my games. By cohort I mean some set of users who registered (or had first activity) on some particular date. For example: users registered on 16 June 2014 is one cohort, and I want to calculate retention, average revenue per user etc., based on this particular set of users. The same should be done for 17 June, 18 June etc. Cohorts also could be calculated on monthly basis, e.g. cohort of May, June etc.

To begin with, I decided to do some basic retention analysis, but I am struggling with MySQL as I m not pro in it.

I have MySQL table with gamer activity entries. The simplified structure looks like this:

| id | game_id | gamer_id |          day        |
| 1  |    1    |    1     | 2014-01-01 00:00:01 |
| 2  |    2    |    1     | 2014-02-02 00:00:03 |
| 3  |    3    |    1     | 2014-01-03 00:00:02 |
| 4  |    7    |    2     | 2014-01-03 00:00:02 |
| 5  |    7    |    2     | 2014-01-03 00:00:05 |

An entry in this table represents some activity in the game. As you can see, there are several games and the same player could play different games. I want to calculate retention profiles for each game.

The result table  should look something like this

By retention I mean number of users who remain in the game by the end of some particular period: month 1, month 2, month 3 after first activity in month 0. Number of users in month 0 is 100%. If I say that month 1 retention is 33%, I say that 33% of users still remain in the game by the end of month 1 (the following after month 0).

Can you suggest some sources or example queries to start digging?


texts: Mongo db custom role with different privilages on differenct collections

texts:
I want to create a custom role which can just read one collection and readWrite all other collections. Don't have any delete permission on any collections.

Why this code won't work?
Should I add something like * in : collection:"" ?

db.createRole(
{
 role: "role1",
 privileges: [
   { resource: { db: "db1" ,collection:"" }, actions: [ "find","insert","update" ] },
   { resource: { db: "db1" ,collection:"col1" }, actions: [ "find"] }
 ],
 roles:[]
},
{ wtimeout: 5000 }
)


texts: Is a year-to-date dimension the same as a time dimension?

texts:
If I have a table such as...:

YYMM | Sales
------------
1201 | 10
1202 | 20
1203 | 30
1204 | 40

... then I might describe this as having a time dimension. If I wanted to calculate a year-to-date value of Sales, would "year to date" be a different dimension to time, or is it actually the same and the additional measure implies the different meaning? Such as:

YYMM | Sales | SalesYTD
-----------------------
1201 | 10    | 10
1202 | 20    | 30
1203 | 30    | 60
1204 | 40    | 100


texts: Is the system column "ctid" legitimate for identifying rows to delete?

texts:
I have a table with hundreds of millions of rows that I need to delete data from.

The existing indexes are the most efficient.

I can however use the existing indexes to find the rows to delete by using the ctid values:

DELETE FROM calendar_event WHERE ctid IN
(SELECT ctid FROM calendar_event WHERE user_id = 5 LIMIT 100 FOR UPDATE)

What are the risks of relying on the ctid in this case? My worst case scenario is deleting the wrong the row.


texts: Creating dummy from JSON values and using it in the WHERE clause

texts:
The variable I'm interested in is an answer to a questionary, stored in a JSON column in a table called Questions. What I need is the number of users who've answered 'no' to a question coded as hasDefaulted. The way I see it, I need to first parse the JSON column, create a dummy and then use it in the WHERE clause, but the exact syntax is just beyond me. Since the data is proprietary, I'll hide the FROM clause - please trust me it's correctly written.

SELECT
COUNT(*) as count,
CREATE INDEX dummy ON dummy(cast("answer"->>'no' AS int))

FROM CLAUSE HERE

WHERE
_____CONDITION1, omitted____
AND "Questions"."code" = 'hasDefaulted'
AND dummy = 1


texts: ORA-39001: ORA-39000: ORA-31640: ORA-27041: and Linux-x86_64 Error: 13: Permission denied

texts:
I used this command to export data:

expdp gsw/password DIRECTORY=gsw DUMPFILE=gsw.dmp logfile=gsw.log SCHEMAS=GSW

Now importing this to other server as:

[root@linux gsw]# impdp gsw/password remap_tablespace=SYSTEM:GSW remap_tablespace=USERS:GSW dumpfile=GSW-31-08-2018.dmp directory=gsw

Import: Release 11.2.0.2.0 - Production on Sat Sep 1 11:17:47 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/app/oracle/admin/XE/dpdump/gsw/GSW-31-08-2018.dmp" for read

ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9

I googled but not able to fix it. Please advise

Thanks in anticipation


texts: Is the "comments" field in pgAdmin 4 a pgAdmin thing, or is it a PostgreSQL feature?

texts:
If I right-click a table (or any object) in pgAdmin 4 and select "Properties", it brings up a GUI window which allows me to change the name of the table and many other things.
One of the fields is called "Comment". The manual for pgAdmin 4 just states this useless description:

Store notes about the table in the Comment field.

Where exactly is this stored? In the actual PostgreSQL database? Or some separate, special pgAdmin storage?
It seems to have nothing to do with the SQL comments such as:
-- This is an SQL comment.

Also, if I add such SQL comments inside my query, are they discarded by both PostgreSQL and pgAdmin, or added somewhere? For example, this query:
CREATE TABLE blablabla ...; -- This table is so cool, dude.

Would "This table is so cool, dude." just be stripped away by both pgAdmin and PG?
I looked in the PG manual but found no special "COMMENTS" feature, where the comment is actually stored in PG. For example, something like this:
CREATE TABLE blablabla ... WITH COMMENT 'This table is so cool, dude.';

Please clarify.


texts:
Before: mysql 5.6. One of the columns in the table is created as follows:

`f_stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

When i view this field in mysql client or using DataGrip, this field is displayed correctly.

Now: mysql 5.7. The field is not displayed correctly (more precisely, it is causes en error). I delete from mysql config following flags: NO_ZERO_DATE, NP_ZERO_IN_DATE and STRICT_TRANS_TABLES. And now, looking at this field i see:

Using mysql-client: 0000-00-00 00:00:00
DataGrip: null

How to change server's option for support this non-standard datetime value, initialize by '0000-00-00 00:00:00' ?

UPD: https://youtrack.jetbrains.com/issue/DBE-3377


texts: Same table alias for two different tables (Oracle)

texts:
This example has the same table alias used for two different tables.  I don't understand why this is allowed by Oracle, and if allowed, how the results make any sense.

create table Table_A (x number);
create table Table_B (x number);
insert into Table_A values (1);
insert into Table_A values (2);
insert into Table_B values (2);
insert into Table_B values (3);

select * from Table_A ;
         X
----------
         1
         2
2 rows selected.

select * from Table_B ;
         X
----------
         2
         3
2 rows selected.

select *
  from Table_A  T
  join Table_B  T
    on T.x = T.x;
         X          X
---------- ----------
         2          2
         2          2
         3          3
         3          3
4 rows selected.


texts: How to iterate over an array of strings and returns matching word

texts:
I'm testing text search on PostgreSQL and I would like to search for multiple terms based on a custom dictionnary

I have :

CREATE TABLE events (name text NOT NULL, description text NOT NULL);

INSERT INTO events VALUES
  ('Phaeleh Live','Phaeleh, one of the UK''s most vital production talents and a key innovator')
 ,('Radar 009','Radar Radio''s weekly parties re-launch for 2018 with some of their biggest line-ups yet.')
 ,('14anger','Collector of techno classics, admirer of futuristic sequencing, he weaves these together for an eternal timed prophecy rave');

CREATE MATERIALIZED VIEW unique_lexeme AS
SELECT word FROM ts_stat(
$TS_STAT$
SELECT to_tsvector('simple', events.name) || to_tsvector('english', events.description) as document
  FROM events
$TS_STAT$);

Then for the query :

WITH word_similarity AS (
  SELECT word
  FROM unique_lexeme
  WHERE similarity(word, 'par') > 0.1
  ORDER BY word <-> 'par'
  LIMIT 1
)
SELECT events_name,
       events_description
       FROM (
       SELECT events.name AS events_name,
         events.description AS events_description,
         to_tsvector('simple', events.name) || to_tsvector('english', events.description) as document
  FROM events) events_search, word_similarity
WHERE events_search.document @@ to_tsquery('english', word_similarity.word || ':*')
ORDER BY ts_rank(events_search.document, to_tsquery('english', word_similarity.word || ':*')) DESC;

But how could I make word_similarity able to return a list of matching term I will search after?
For example if I provide ['wee','vit'] I will get ['week','vital'].

Can I do it with a recursive CTE? or do I need to write a for each loop in PL/pgSQL?

sqlfiddle