texts:
My machine does not have full SQL Server installed. The following distributable packages are installed on my machine:-

Microsoft® SQL Server® 2008 R2 Command Line Utilities
Microsoft® SQL Server® 2008 R2 Native Client

I have a batch file that runs the bcp utility. If i run this batch file from the folder where bcp.exe is sitting (...tools\binn) it works.

My dump question is if there is any way I can run the bcp utility from c:\ drive only. Right now if it run from c:\ drive it says:-

  bcp is not recognized as an internal or external command,..


texts: SQL Server add zero if there is length less than specific number

texts:
I have a table with a column of numbers like 789. I want to pad them with 0 to equal 9 digits. For example, 789 should be 789000000

I have tried this code, but there are some numbers more than 3 digits and less than 3 digits.

UPDATE
   Table
SET
   MyCol = MyCol+'0000000'
WHERE
   LEN(MyCol) >9;


texts: Why is Flashback Data Archive telling me the table doesn't exist

texts:
select * from v$version;

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

create table test1 as select * from dual;

Table TEST1 created.

alter table test1 flashback archive archive_1_month;

Table TEST1 altered.

select * from test1;

DUMMY
X

update test1 set dummy = 'Y';
commit;

1 row updated.
Commit complete.

select * from test1

DUMMY
Y

Now this isn't working:
select * FROM test1 VERSIONS BETWEEN TIMESTAMP TRUNC(SYSTIMESTAMP) AND SYSTIMESTAMP;

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:
Error at Line: 48 Column: 53

This also doesn't work:
select * from test1 as of TRUNC(SYSTIMESTAMP);
(same error result)
Additional info:
SELECT owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
FROM   dba_flashback_archive_tables
ORDER BY owner_name, table_name;

SELECT owner_name,
       flashback_archive_name,
       flashback_archive#,
       retention_in_days,
       TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time,
       TO_CHAR(last_purge_time, 'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,
       status
FROM   dba_flashback_archive
ORDER BY owner_name, flashback_archive_name;

select * from FLASHBACK_TRANSACTION_QUERY where table_name = 'TEST1';

There also seem to be missing details (like table_owner, and undo_sql) in the flashback_transaction_query table.


texts: create a FTS in sql2005 on multile fields in multiple tables?

texts: How can I create a Full Text Search catalog on 3 fields where one is in a second table?


texts: What does it mean there is multiple sources in a SELECT's "FROM" clause?

texts:
I have a table with a jsonb column, and I wanted to select from this table with the contents of that column extracted into columns:

CREATE TEMPORARY TABLE stuff (
    name text,
    contents jsonb
);

INSERT INTO stuff (name, contents)
    VALUES ('hello', '{"x": "hello", "y": "postgres", "z": "rdbms"}'::jsonb);

INSERT INTO stuff (name, contents)
    VALUES ('hello', '{"x": "hello2", "y": "postgres2", "z": "rdbms2"}'::jsonb);

CREATE OR REPLACE FUNCTION pg_temp.extract_stuff (s jsonb, out x text, out y text, out z text)
    RETURNS record
AS $$
BEGIN
    x = s ->> 'x';
    y = s ->> 'y';
    z = s ->> 'z';
END;
$$
LANGUAGE plpgsql;

I was screwing around and somehow landed on this statement, which works perfectly and looks very intuitive:

SELECT
    name,
    foobar.x,
    foobar.y,
    foobar.z
FROM
    stuff,
    pg_temp.extract_stuff (contents) foobar;

 name  |   x    |     y     |   z
-------+--------+-----------+--------
 hello | hello  | postgres  | rdbms
 hello | hello2 | postgres2 | rdbms2

However, I'm not sure I understand how this works. Is it shorthand for an implicit inner join of some sort? What is the limitation of this type of syntax (can there be multiple physical tables, etc)?


texts: ERROR: Failed to convert parameter value from a String to a DateTime

texts:
I have faced this error: Failed to convert parameter value from a String to a DateTime when trying to pass multiple selected dates from checkboxlist to my parameter to be used in sql.

I have tried it with other datatypes such as nvarchar and it works when I pass multiple selected values to 1 stored procedure parameter and return the select statement using dynamic sql to populate my gridview.

Ps. In my webserver i'm displaying in checkboxlist as e.g 31-Aug-2013, using DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";. In my sql database, it is displayed as e.g 2013-08-31.

During debugging selectedDATE do read it as e.g 2013-08-31. however there is error at da.Fill(ds); line

ASPX.CS

    protected void Page_Load(object sender, EventArgs e)
    {
        DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";

        using (SqlConnection conn = new SqlConnection(dbConn))
        {
            try //Call stored procedure
            {

                SqlCommand cmd = new SqlCommand(spddl, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (!IsPostBack)
                {
                    DATE.DataSource = ds.Tables[0];
                    DATE.DataTextField = ds.Tables[0].Columns["DATE"].ToString();
                    DATE.DataBind();

                }
                if (IsPostBack)
                {
                    Bind();
                }

            }

            catch (Exception i)
            {
                bool exception = true;
                if (exception == true)
                {
                    //txtMessage.Text += e.Message;
                }
            }
        }
    }

public void Bind()
   {
           using (SqlConnection conn = new SqlConnection(dbConn))
       {
           using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
           {
                 String selectedDATE = String.Empty;

                if (DATE.SelectedValue == "All")
                {
                    selectedDATE = "DATE";
                }
                else
                {
                    foreach (ListItem item in DATE.Items)
                    {
                        if (item.Selected)
                        {
                            DateTime dtTemp = Convert.ToDateTime(item.Value);
                            selectedDATE += "'" + dtTemp.ToString("yyyy-MM-dd") + "',";
                        }
                    }

                    selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);
                }

               cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.DateTime).Value = selectedDATE;

               conn.Open();
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               DataSet ds= new DataSet();
               da.Fill(ds);
               GRIDVIEW.DataSource = ds.Tables[0];
               GRIDVIEW.DataBind();

           }
       }

SQL

ALTER PROCEDURE [dbo].[SP]

@param nvarchar(512)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM TABLENAME WHERE [COLUMN] IN (' + @param + ')'
EXEC sp_executesql @sql;
END


texts:
I have made a script which harvests mac addresses from switch ports and puts them in a database. I insert it into an import table to be copied over later to the real table. All fine. Now I am writing the query to find which mac addresses are new. So, I need the rows in the import table which are not in the destination table. A left outer join makes sense:

select *
from SwitchportMac_import i
left outer join SwitchportMac sm
on sm.switch = i.switch and sm.port = i.port and sm.mac = i.mac

This returns also rows in i which match in sm. Sure, I can add a where sm.id is null but I am flabbergasted.

So either:

I am an idiot and did something obviously wrong
I am an idiot and have been explaining and understanding joins wrong for 20 years

If it's the first I will dump the DDL and figure it out but I'm scared it's the second one. Does the outer keyword exist just to confuse me?


texts:
Is it possible to get rid of so-called 'Spurious Tuples' completely?

For example: In this example from a textbook, there is an original table:

I don't see anything wrong with the two projections of it:

But their join is still generating spurious tuples:

(These figures from ToddEverett's answer.]


texts: "Unexpected token" in a DB2 SQL statement that runs OK in Postgres

texts:
I trying execute statement like following (i obfuscated it a little) in DB2:

UPDATE FIRSTTABLE SET fstatus=1 WHERE fid in (SELECT T.fid
  FROM FIRSTTABLE T
  INNER JOIN SECONDTABLE V ON V.efvid = T.efvid
  INNER JOIN OTHERTABLE E ON E.efid = V.efid
WHERE (efname = 'event')  AND  (eftname = 'eventhtml') FOR READ ONLY)

And receiving error:

42601][-104] An unexpected token "UPDATE FIRSTTABLE SET fstatus" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<query_into_expr_top>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.7.85

But, with, for example, postgresql this script works fine.
P.S. I am using DB2 Express, if it matters.


texts: How to set minimal token length for to_tsvector() function?

texts:
I parse xml documents with to_tsvector() function, and, sometimes, it produces tokens, which are shorter than 3 characters:

'1':89,91 '2019':14 '25':4

I know, that

to_tsvector([ config regconfig, ] document text) returns tsvector

accepts config as first param, but i can't find solution for setting a minimal token length here. Is there any way to do it?


texts: AUTO INCREMENT concurrency issue and ordering

texts:
Let's suppose that table t1 has an AUTO INCREMENT primary key id. And let's say that I am starting two transactions at different moments of time, and that transactions are started by the same application code. Long running select has different time duration, so it can take more or less time, unpredictable.

Session 1                    Session 2
start transaction;
(long running select)
                             start transaction;
                             (long running select)
                             insert into t ...
insert into t ...            commit;
commit;

From this point, if I do select * from t I would see that AUTO INCREMENT field id value related to Session 1 is bigger than value related to Session 2, thus reversing order of connections.

How to prevent that?

Here is the real showcase:

> CREATE TABLE t ( c1 INT NOT NULL AUTO_INCREMENT, c2 VARCHAR, PRIMARY KEY (c1) ) ENGINE = InnoDB;

Session 1                               Session 2
start transaction;
                                        start transaction;
                                        insert into t values('2nd transaction')
insert into t values('1st transaction') commit;
commit;

> SELECT * FROM t;
+----+--------------------+
| c1 | c2                 |
+----+--------------------+
|  1 |    2nd transaction |
|  2 |    1st transaction |
+----+--------------------+

What I really need is some locking mechanism, so it would look like this:

Session 1                               Session 2
start transaction;
** some locking **
                                        start transaction;
                                        ** some locking **
                                        insert into t values('2nd transaction')
                                        ** wait for lock releasing **
insert into t values('1st transaction')
commit;
                                        ** lock released **
                                        commit;

> SELECT * FROM t;
+----+--------------------+
| c1 | c2                 |
+----+--------------------+
|  1 |    1st transaction |
|  2 |    2nd transaction |
+----+--------------------+

If I do LOCK TABLES t WRITE as soon as transactions are started, that solves the issue, but creates another one, related to metadata locking from other transactions, like this:

Session 1                               Session 2
start transaction;
select * from t;
                                        start transaction;
                                        lock tables t write;
                                        ** hangs, waiting for metadata lock releasing **

commit;
                                        ** metadata lock on t released **
                                        <next statements>
                                        commit;

and this is creating serious issues when doing backup with mysqldump --single-transaction

Regarding the suggestion to change the application logic to not use id values in this way, and add a timestamp column in the table and use that instead:

The developers informed me that table in question serves as queue. That's why the order is important. Would moving it to some more MQ related technologies (like RabbitMQ, or even redis) be worth it?


texts: How to check oracle error description in sqlplus?

texts:
I'm trying to find the oracle error's description in SQL*PLUS.  Similar command in DB2 as

? SQLXXXX

Where XXXX is the error number.


texts: Converting old SQL 2000 Non-Ansi code to SQL 2012 compatible

texts:
We have a legacy application that is only used for a read only reference.

It is using SQL Server 2000.
We want to migrate its database to SQL version 2012, however we can't upgrade the software and the vendor doesn't exist anymore.

The problem is that the views/stored procedures on the database are using old NON-ANSI SQL code that isn't supported anymore.

When we tried to simply restore it on SQL 2008R2 (as there isn't a direct restore from 2000 to 2012), and increase the compatibility level to 100, we got validation errors on the views/stored procedures, as they're using old join syntax.

  The query uses non-ANSI outer join operators ("=" or "="). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

As there are ~200 objects that need to be updated with a newer and proper join syntax code, I was wondering whether a tool/script/code-generator exists somewhere that will help me convert the code.

I did notice that if I right click the view object and select Design, the query of the view IS converted to the proper syntax.
But that isn't automated enough for me.

Any thoughts?


texts: How to change VARCHAR length in PRIMARY KEY in SQL Server 2014

texts:
I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is linked to.

Is there a way to change the VARCHAR length of a PRIMARY KEY without dropping all the tables?


texts: Why is there a difference in the character limit for VARCHAR2 in SQL and PL/SQL?

texts:
While working with some large scripts I encountered errors related with the 4000 character limit that VARCHAR2 has in SQL but not in PL/SQL.

As explained here the VARCHAR2 datatype has a 4000 character limited, while the PL/SQL has a much larger limit of 32,767.

Why does that difference exist? Wouldn't have been better to have both VARCHAR2 with the same limit? It seems a rather confusing and prone to error decision.

Update

I updated my question to add ansible's comment. Oracle VARCHAR2 and PL/SQL VARCHAR2 limit are on bytes, not in characters. This can have significant impact in multibyte characters system.


texts: Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

texts:
I am using Oracle 11g and want to export a table using below statement

C:\>expdp SYS/sys@MYDATABASE DIRECTORY = Mydir DUMPFILE = Customer.dmp LOGFILE = Customer.log TABLES = PERSON.CUSTOMER

After this statement cmd is giving this

UDE-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

I proceeded with

Username:  SYS@SMSPSETUP AS SYSDBA
Password:

And finally I got

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

I have created directory 'Mydir' as below

SQL>CREATE DIRECTORY Mydir AS 'C:\Users\osman\Desktop\Export';

And gave everyone full privileges to this folder from the windows folder system.

When run the below statement:

select directory_name, directory_path from dba_directories; where directory_name='Mydir';

I can see the path 'C:\Users\osman\Desktop\Export'

So why am I getting this error?


texts:
CREATE OR REPLACE procedure create_table_dateWise27()

LANGUAGE plpgsql

as $$

declare

d_date date;

begin

    select to_char(current_timestamp :: timestamp - interval '1 hour','yyyy-mm-
dd')into d_date;

EXECUTE format('
   CREATE TABLE IF NOT EXISTS %I(
    nodeip text,
    interface text,
    nodeip_interface text
   )','test_'|| d_date-1);

commit;

end;
$$;

call create_table_dateWise27();

I want to perform the same operation where d_date is a VARCHAR().


texts: Dynamic casting from text within plpgsql

texts:
Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query of the form:

UPDATE table SET attribute = x WHERE table_id = y

This is so common that I have a stored procedure that performs this task:

CREATE OR REPLACE FUNCTION setvalue(
    relname text,
    row_id integer,
    colname text,
    newvalue text)
RETURNS void AS
$BODY$
BEGIN
  EXECUTE format('UPDATE %I SET %I = $1 WHERE %I = $2', relname, colname, relname || '_id', colname) USING row_id;
END;
$BODY$
LANGUAGE plpgsql;

Unfortunately this doesn't work for non-text types. For instance, updating a date column gives ERROR: column ... is of type date but expression is of type text. Assuming the text is a valid literal representation of the intended type, is there a safe way to get the DBMS to figure out the right type and do the right thing?


texts: Find all `NaN` values in a postgresql database

texts:
Numerics and floats can store NaN https://www.postgresql.org/docs/current/static/datatype-numeric.html

I have a working program with a PostgreSQL database that does not have any constraint about NaN.

I want to know which fields of which tables contains NaN (looking at some schemas, not at public schema).

Is there an automatic way to do that?

Later I add a constraint to that fields of that tables.


texts: How to compare and MERGE two SQL Server environments (not just databases)

texts:
Many similar questions were asked before, and mostly about comparing database schemas and data. That's all fine, but what about all the other SQL Server objects in the system overall?

This is the closest answer I've found, but it doesn't answer my query on a tool to be able to also create an SQL script of the variances, that can be easily applied to another server - including new user logins, roles, schemas, linked-servers, maps, maintenance plans, etc.

The other tool I found that does this is TOAD for SQL Server, but it's not cheap. I was hoping for an SQL script solution? Particularly with a mix of SQL Server environments from 2008 R2 to 2017?

Thanks


texts: Why does PostgreSQL not use an index when OR is used in the conditions?

texts:
DB structure summarized:

the main table is cases (about 136k rows)
each case can have 0 - n referencing rows in the table case_contacts
each case contact references a main contact in the table contacts
a case contact may also reference a secondary subcontact, also in the table contacts
contacts have their names in contacts.v_fullname, which is indexed with
a trigram index

The goal is to find cases where the name of a contact or subcontact contains the string "test":

SELECT  c.id,
        c.number
  FROM  cases c
        JOIN case_contacts caco ON caco.case_id = c.id
        JOIN contacts con_main ON con_main.id = caco.contact_id
        LEFT JOIN contacts con_sub ON con_sub.id = caco.subcontact_id
 WHERE  con_main.v_fullname ILIKE '%test%'
        OR con_sub.v_fullname ILIKE '%test%'

This query (query plan) returns the correct result, but does not use the trigram index. It takes around 330ms.

Removing either of the match conditions (query plan), or having them point at the same table (query plan), removes the performance problem. Both of these use the trigram index and are executed in under 1ms, but do not solve the given task.

How can I get PostgreSQL to use my index?

I have simplified this example to the minimum necessary to demonstrate the effect. The actual query is much more complex (and partially auto-generated), so using a UNION of two queries with only one text match each would be very hard, if it's even possible.

I'm using PostgreSQL 9.5.5.
The schema is still open for modifications (to some degree).

As requested, more information about the indexes:

dbname=# \di+ *contacts*
                                         List of relations
 Schema |               Name               | Type  | Owner |     Table     |  Size
--------+----------------------------------+-------+-------+---------------+---------
 public | case_contacts_case_id_idx        | index | x     | case_contacts | 4544 kB
 public | case_contacts_contact_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_id_case_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_idx                | index | x     | case_contacts | 9608 kB
 public | case_contacts_pkey               | index | x     | case_contacts | 4544 kB
 public | case_contacts_reference_trgm_idx | index | x     | case_contacts | 4960 kB
 public | case_contacts_subcontact_id_idx  | index | x     | case_contacts | 4544 kB
 public | case_contacts_type_idx           | index | x     | case_contacts | 6208 kB
 public | case_contacts_unique_types_idx   | index | x     | case_contacts | 5464 kB
 public | contacts_parent_id_id_idx        | index | x     | contacts      | 456 kB
 public | contacts_parent_id_idx           | index | x     | contacts      | 360 kB
 public | contacts_pkey                    | index | x     | contacts      | 360 kB
 public | contacts_v_fullname_trgm_idx     | index | x     | contacts      | 1560 kB
(13 rows)

This is how the index on contacts.v_fullname is created:

CREATE INDEX contacts_v_fullname_trgm_idx ON contacts USING GIN (v_fullname gin_trgm_ops);


texts: How can we get table name from inside trigger?

texts:
I need to pass the table name from trigger to procedure.

ALTER TRIGGER [dbo].[Trg_ProjectCreation]
   ON  [dbo].[Projects]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
 -- procedure call
    exec Proc_Test @Tablename (not need to hardcode)
END

I want to know is there any way I can get table name from trigger itself. I need this to be dynamic because when the table's name changes I don't want to change my code.


texts: I have a query which does not return result with the desired alias names of columns in PostgreSQL

texts:
The following is my query which does not return result with the desired alias column names:

with d as (select distinct emp.ahrims_id, rank.name as rank
from hr_employee as emp, ana_rank as rank,
unit_identification_code as uic, record_record as rec
where emp.unit_identificatin_code_id = uic.id and
rec.hr_employee_id = emp.id and
rank.id = emp.rank_id
and uic.code in ('Z00100','Z00200','Z00300','Z00400','Z00450','Z00500','Z00698','Z00600',
'Z00699','Z00680','Z00700','Z00697','Z00800','Z00900','Z01000','Z01100',
'Z01200','Z01300','Z39000','Z39012','Z10000','Z10005','Z10007','Z10009',
'Z10100','Z10101','Z10200','Z10300','Z10301','ZACA97','ZOCG01','ZACA99',
'ZACA98','ZOCG02','Z10400','Z10500','Z10600','Z10700','Z10800','Z11000',
'Z36000','Z37000','Z20000'))
select(select count(*) as "GSG1 Military NCO" from d where rank in
('MSG','SFC','SGM','SGT','SSG','G1','G2','G3','G4','G5')),
(select count(*) as "GSG1 Military SDR" from d where rank in ('SDR','G6')),
(select count(*) as "GSG1 Civilian NCO" from d where rank in ('Civ11','Civ12','Civ13','Civ14','Civ15')),
(select count(*) as "GSG1 Civilian SDR" from d where rank in ('Civ16'))

It returns:

  ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
     1615 |      695 |        0 |        1

My desired result:

   GSG1 Military NCO | GSG1 Military SDR | GSG1 Civilian NCO | GSG1 Civilian SDR
---------------------+-------------------+-------------------+------------------
     1615            |      695          |        0          |        1


texts: Accelerated Database Recovery - Should I turn it on for system databases

texts: I've just upgraded to SQL Server 2019 and I'm turning on ADR for all the user databases, but my question is, should I also be doing the system databases?


texts: Measure Agent Job failure and running jobs with 'execution_status'

texts:
Considerations

I need to create a Dashboard with job failure AND running jobs.
I have a code to get run_status etc.
I am not interested in succeed jobs.
I extract failure from run_status = 0.

Query as of today:

select
   j.name as 'JobName',run_date, run_time, run_status,
   msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
   run_duration,
   ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100
+ 31 ) / 60) as 'RunDurationMinutes',
   DateADD(MINUTE,((run_duration/10000*3600 + (run_duration/100)%100*60 +
run_duration%100 + 31 ) / 60) ,
   msdb.dbo.agent_datetime(run_date, run_time)) as RunStopDate,
   step_id, Step_name
From
   msdb.dbo.sysjobs j
INNER JOIN
   msdb.dbo.sysjobhistory h
ON
   j.job_id = h.job_id
where
   j.enabled = 1  --Only Enabled Jobs
and
   msdb.dbo.agent_datetime(run_date, run_time)  > DATEADD(DAY,-90, GETDATE())

How and where do I add a case to below query? I want all my columns but also a new execeution_status as below.
I also assume I can get from run_duration the amount an on-going job has run so far.

    else
    CASE h.execution_status
    WHEN j.[execution_status] = 0 THEN ' Returns only those jobs that are not
    idle or suspended. '
    WHEN j.[execution_status] = 1 THEN 'Execution'
    WHEN j.[execution_status] = 2 THEN ' Waiting for thread '
    WHEN j.[execution_status] = 3 THEN ' Between retries '
    WHEN j.[execution_status] = 4 THEN 'Idle'
    WHEN j.[execution_status] = 5 THEN 'Suspenden'
    WHEN j.[execution_status] = 7 THEN 'Performing complet actions'
    End
    AS ExecutionStatus,

Source execution_status: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-job-transact-sql.

We did came up with this simple solution, and I want some feedback about pros and cons.

    SELECT
    ja.session_id,
    ja.job_id,
    j.name AS job_name,
            CASE
        WHEN ja.start_execution_date IS NULL THEN 'Not running'
        WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date
    IS NULL THEN 'Running'
        WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date
    IS NOT NULL THEN 'Finished'
    END AS 'RunStatus'  ,
    ja.last_executed_step_id,
    jh.step_name,
    jh.run_status,
    ja.start_execution_date,
    ja.stop_execution_date,
    ja.job_history_id,
    j.[enabled]
    FROM
    (msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON
    ja.job_history_id = jh.instance_id)
     join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
            WHERE session_id = (SELECT MAX(session_id) FROM
     msdb.dbo.sysjobactivity)
            and j.enabled =1


texts: Good practices for audit data changes for SQL Server 2016

texts:
In my current project we are required to keep the records of data changes for the Employee table at least for specific field for NOW (it can be extended for all the fields later).

We are also required to populate all the historical changes for each employee in Employee View screen by default, where we need to show the OLD and NEW values.

We are thinking for following two approach.

Having History table for Employee with Same schema along with few additional columns. This can be filled either using Triggers or via SPs used for CURD operations. (which one is good Triggers or SPs?)

or

We can have common table with basic schema like follow
TableName,FieldName,ChangeBy,ChangeDT,OldValue,NewValue etc..

Application is being developed in ASP.NET CORE with MSSQL2016.

Which one should be preferred or please share the best alternative.


texts: While under massive write (insert and create table as) load, when Oracle RAC needs to extend tablespace, all write activity gets blocked for however long it takes it to finish extending (tens of minutes), with all sessions blocked by "data file init write".  Is there way to configure Oracle to extend the file proactively so that things can keep moving while it is doing it?


texts: List with all visible meta data in AFTER UPDATE trigger

texts:
I have the following issue - something is updating particular table columns when such update is not allowed. I was not able to find the object (searching in T-SQL and ASP code, server jobs), so I decided to create AFTER UPDATE trigger on the given table and try to log everything that is possible and will help me to find what is updating the table.

I am wondering what metadata can I access in the trigger - for example: T-SQL statement performing the update?

Also transaction log back up are made on each 15 minutes, so I guess it is possible to detect the change from the backups having particular information like time and transaction id?


texts: Fetching Data from a Remote Server and Inserting it into local database Table directly

texts:
I trying to run a select "concat insert query" withing select statement from a local mysql server and fetch some fragmentation related meta data from a remote server and insert that data into database tables on local server, i haven't tried this approach before and no web pages found regarding this online , can i have some suggestion from anyone who is familier with this approach.

My whole idea was to run a select query on local server and fetch some data from remote server and directly insert that data into local server database tables .

I am using MySQL on both the server,

i am pasting my query here can someone help me regarding this

Query:

mysql -sN -uxxxx -pxxxxx -h xxxxxx -e "
select concat('insert into reporting.fragmentation values(' "'", @@hostname ,"'" ',' "'", table_schema ,"'" ',' "'", table_name ,"'" ','
"'", concat(round(table_rows/1000000,2), 'M') ,"'" ',' "'", concat(round((data_length+index_length)/(1024*1024*1024), 2), 'G') ,"'" ','
"'", concat(round(data_length / (1024*1024*1024),2),'G') ,"'" ',' "'", concat(round(index_length / (1024*1024*1024),2),'G') ,"'" ','
"'", round(data_length/1024/1024) ,"'" ',' "'", round(index_length/1024/1024) ,"'" ',' "'", (data_free/(index_length+data_length)) ,"'" ','
"'", now() ,"'" ');')
from information_schema.tables
where TABLE_SCHEMA NOT IN ('information_schema','mysql','sys','performance_schema')
and
round(data_free/1024/1024) > 500
and
table_schema like 'livedriveusers_%'"


texts: SQL Server 2012 is putting [brackets] around Table and Column Names

texts:
When you drag the Columns folder to editor Window, the full list of Columns are added without Brackets.  If you drag columns individually they have Brackets.

Is there anyway to turn that off?  I'm unable to find anything in options, and they're really a distraction.


texts: dynamic postgres inside shell script not working

texts:
PG_CMD="psql -d portal -U portal -c "
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD "
        DO \$$

           BEGIN
                   select * from customer;
          END;
        \$$";
}
*******MAIN***
abc

I want to print result of query on command line as well as I want to send it to excel file.

ERROR:  query has no destination for result data


texts: Regular Expression Integrity Constraints in SQLite3

texts: In a SQLite3 CREATE TABLE statement, can I use a regular expression to enforce a particular constraint? Specifically, I am trying to require that an inputted URL is valid. I know there are other ways of doing this besides using regular expressions, but because of the structure of my project, this is the method I need.


texts: Do covering indexes in PostgreSQL help JOIN columns?

texts:
I have a whole lot of tables that look vaguely like this:

CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES table1(id), t2c1 INTEGER);

And I do a whole lot of joins where I'm trying to filter on the joined-in table to get stuff from the first table, like this:

SELECT t1c1
FROM table1
JOIN table2 ON table2.t1 = table1.id
WHERE t2c1 = 42;

When I go to write indexes for a table I'd look at the columns that get used in the WHERE clause and build out indexes to satisfy them. So for this query I'd wind up writing an index like this:

CREATE INDEX ON table2 (t2c1);

And this index is at least eligible for use in that query.

My question is that if I write an index like this:

CREATE INDEX ON table2 (t2c1, t1);

Will the index be used as a covering index to help the JOIN in the above query? Should I change my index writing strategy to cover foreign key columns?


texts: When _id is present in the projection, is the index on _id used for the query?

texts:
My query is

db.new.find({'a':{'$gt':200}, 'b':{'$lt': 30}},
            {'a':1, 'c':1}
).sort({'c':-1})

Indexes present are (a,b), (a,c), (c), (a,b,c), (_id)

When I check with explain('allPlansExecution'), I see that the index on _id is not used anywhere. However, since _id has not been suppressed in the projection, shouldn't it be used? Is there any possibility of it being used implicitly in such cases?


texts: How do I find a list of customers that have not placed an order in the past 1 year?

texts:
I'm having trouble figuring out how to write this query:

We have a sales table but the sales table only represents customers that have purchased.

We have a customers table that represents all of the customers that we have.

I have tried:

select
    c.customer_id
from
    customers c
left join
    orders o
    on c.customer_id=o.customer_id
    and c.created_at >= now()-interval '1 year'
group by
    c.customer_id
having
    count(distinct o.order_id)=0

But for a table with many millions of rows, it is very slow even with index.

I'm assuming it's because of the left join.

What can I do?


texts:
I get the following error when I execute my package as a SQL server agent job.

It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.

I don't even know how to begin fixing this error.

Where should I check first?

Date        a value of time
Log     Job History (MyJob)

Step ID     1
Server      PCTSQL004
Job Name        MyJob
Step Name       Job_1
Duration        00:00:00
Sql Severity        0
Sql Message ID      0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted       0

Message
Executed as user: CS\DmcSysManager. The process could not be created for step 1 of job 0x63BB5A86DB23F947866D2A806BE4CC6B (reason: A required privilege is not held by the client).  The step failed.


texts:
Just as the title says, where can I see it ?

Are there any config options for it ( like how many ms would determine if a query is slow or not ) ?


texts: change file table file stream data folder faces error The container can not be dropped

texts:
I want to change file database file stream folder from Drive D to Drive F. I successfully transferred data to new folder but when I want to Remove old file stream folder I face this error:
"The container can not be dropped because changes exist that require a log backup. Take a log backup and then retry the ALTER DATABASE operation."
When I want to get transaction log, It encounter this error: you should enable filestream on this instance, while filestream is enabled.
This my code:
ALTER DATABASE MyDatabase
REMOVE FILE MyDatabase_Files;
GO

Thanks for any helps.


texts: MySQL Count(Table1.ColId) Where IN (any of Table2.Col1 ,Table2.Col2)

texts:
I have 2 Tables

Table 1 has Id, Name
Table 2 has Id, Col1_IdVal, Col2_IdVal,Col3_IdVal,Col4_IdVal

I would like simple solution for

Count(T1.Id) WHERE T1.Id In (T2.Col1_IdVal,T2.Col2_IdVal,T2.Col3_IdVal,T2.Col4_IdVal)

Something I have tried does not return correct result set , too few in the result :

SELECT COUNT(T1.Id) FROM Table1 As T1
LEFT JOIN Table2 AS T2_1 ON T2_1.T2Column_1 = T1.Id
LEFT JOIN Table2 AS T2_2 ON T2_2.T2Column_2 = T1.Id
LEFT JOIN Table2 AS T2_3 ON T2_3.T2Column_3 = T1.Id
LEFT JOIN Table2 AS T2_4 ON T2_4.T2Column_4 = T1.Id
LEFT JOIN Table2 AS T2_5 ON T2_5.T2Column_5 = T1.Id;

Table 1 has Unique Values - it is Primary Key, Table 2 can have any number of values MANY.  Table2 columns are not foreign keys of Table1 - they are values that must exist in Table1 but other than that - they are not FKeys.

So basically Count(Id) Where Id Value Is in T2, Cols 1..n


texts: Filtering characters to just return numeric characters in a string?

texts:
I want to filter an nvarchar field to return just the numeric values.

I have some SQL to do this but it seems way more complicated than it needs to be. I am interested in finding out if anyone has a better way to filter out any non-numeric characters in a string?

IF OBJECT_ID('tempdb..#MOB') IS NOT NULL
BEGIN
    DROP Table #MOB
END

SELECT [mob]
INTO #MOB
FROM (
SELECT '(00) 1234 5678' AS [mob]
UNION
SELECT '1234 5678' AS [mob]
UNION
SELECT '+61 012 345 678' AS [mob]
) AS temp

;WITH [fill] ([Num], [Index], [MOBILEPHONE])
AS
(
    SELECT
    CASE
        WHEN [MOBILEPHONE] IS NOT NULL
        THEN SUBSTRING([MOBILEPHONE], 1, 1)
        ELSE NULL
    END AS [Num]
    , 1 AS [INDEX], [MOBILEPHONE]
    FROM (
        SELECT DISTINCT [mob] AS [MOBILEPHONE]
        FROM #MOB as t
    ) AS temp
    UNION ALL
    SELECT
    SUBSTRING([F].[MOBILEPHONE], [F].[Index] + 1, 1) AS [Num]
    ,[F].[Index] + 1 AS [Index]
    , [MOBILEPHONE]
    FROM [fill] AS [F]
    WHERE ([F].[Index] + 1) < LEN([F].[MOBILEPHONE]) + 1
)

SELECT [E].[MOBILEPHONE] AS [old_MOBILEPHONE],
    STUFF((SELECT N'' + [F].[Num]
    FROM [fill] AS [F]
    WHERE (PATINDEX('%[^0-9]%', [F].[Num]) = 0 OR PATINDEX('%[^0-9]%', [F].[Num]) IS NULL) AND
    ([F].[MOBILEPHONE] = [E].[MOBILEPHONE])
    ORDER BY [F].[MOBILEPHONE], [F].[Index]
    FOR XML PATH('')), 1, 0, '')
    AS [MOBILEPHONE]
FROM (
        SELECT DISTINCT [t].[MOBILEPHONE]
        FROM (SELECT [mob] AS [MOBILEPHONE] FROM #MOB) as t
    ) AS [E]

Output

╔═════════════════╦═════════════╗
║ old_MOBILEPHONE ║ MOBILEPHONE ║
╠═════════════════╬═════════════╣
║ (00) 1234 5678  ║  0012345678 ║
║ +61 012 345 678 ║ 61012345678 ║
║ 1234 5678       ║    12345678 ║
╚═════════════════╩═════════════╝

I have seen the Q & A T-SQL select query to remove non-numeric characters on Stack Overflow, but that answer has a similar solution to what I have found, using a CTE table and recursion. I am searching for something simpler. Hoping there is something like a custom collation that I can create or a regex filter I can apply?


texts: How to solve "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" error?

texts:
I am trying to create connection with the database for debugging ASP.Net website. Database is 11g Oracle DB... There is no problem when I connect using SQL Developer, however using Visual Studio I get the error of ORA-12514 (TNS:listener does not currently know of service requested in connect descriptor). 11g ODAC for Visual Studio 2010 has been configured as well.  There are similar questions about this error, I could not find solution for my problem.

Thanks in advance for the help!!!


texts: Insert-heavy InnoDB table won't use all my CPU

texts:
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could be damaging to our customers.  In a performance tuning scenario, I send 1,000,000 packets to the server over multiple DB connections. But no matter what settings I use in my.cnf, I can't get the mysqld process to use more than 900% CPU on a system with 12 cores. (Nothing else is running on the box.)

I've set the following

innodb_file_per_table = 1
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_thread_concurrency = 0

If I use MyISAM, I can get all the packets written in about 6 seconds. But InnoDB takes about 25. Can I make MySQL use the rest of the system resources and insert faster?

Edit: Here's the schema for the table:

+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| t     | bigint(20) unsigned  | YES  |     | NULL    |       |
| a     | char(1)              | YES  |     | NULL    |       |
| sa    | int(10) unsigned     | YES  |     | NULL    |       |
| sb    | int(10) unsigned     | YES  |     | NULL    |       |
| sc    | int(10) unsigned     | YES  |     | NULL    |       |
| sd    | int(10) unsigned     | YES  |     | NULL    |       |
| sp    | smallint(5) unsigned | YES  |     | NULL    |       |
| da    | int(10) unsigned     | YES  |     | NULL    |       |
| db    | int(10) unsigned     | YES  |     | NULL    |       |
| dc    | int(10) unsigned     | YES  |     | NULL    |       |
| dd    | int(10) unsigned     | YES  |     | NULL    |       |
| dp    | smallint(5) unsigned | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+

edit2:
I've batched more inserts together so that a single query is near the maximum length (about 16,000,000 chars). The database now spikes to 1100% for two seconds, then goes down to 100% for the rest of the time. Total time is now 21 seconds, or about 16% faster than when I started.


texts: Sql Server 2012 Failover Replica in Synchronous Mode between Primary-Secondary on VPN is very slow

texts:
I have installed SQL Server 2012 Clustered HA with Replica in Sync mode between two nodes connected by an high-speed VPN. The DB is encrypted. Everything works fine, but to do the replication, the HA system takes a lot of time.

The DB is made up of 90 tables of which only 10 are fast-moving. The average for the 10 tables is equal to 100,000 records. The others have from 1,000 to 25,000 records. Can you explain to me what is due to the slowness?

The Primary Server how often starts the synchronization mechanism with Secondary and the Secondary how often start the same sync with Primary server (I have chosen MASTER-MASTER HA replica)?


texts:
I need to know Is oracle exadata database is same as the oracle database which is installed in my laptop. What is the main difference.
Can I simply take backup from exadata database and restore it in my laptop?


texts: How to select users that doesn't in the role

texts:
I have 4 roles which are Admin, Procurement, Accounting, HR roles and 3 tables which are Users, Roles and Userroles. They user can have many roles as well as the roles can have many users. the problem is how can i get the users that are not in the role for example is the procurement because in my query I still get the users that are in procurement role? Here is my query.

  Admin role_id = 1

  Procurement role_id = 2

  Accounting role_id = 3

  HR role_id = 4

SELECT
  `users`.`user_id`,
  `users`.`username`,
  `users`.`email`,
  `users`.`last_login`,
  `users`.`last_ip`,
  `users`.`created_on`,
  `users`.`status` AS user_status,
  `roles`.`role_id`,
  `roles`.`role_name`,
  `roles`.`role_code`,
  `roles`.`status` AS role_status
FROM
  (`users`)
  INNER JOIN `userroles`
    ON `users`.`user_id` = `userroles`.`user_id`
  INNER JOIN `roles`
    ON `userroles`.`role_id` = `roles`.`role_id`
WHERE `roles`.`role_id` != '2'
ORDER BY `users`.`user_id` ASC

Let's say that we have user1, user2, and user3.

  User = Role

  user1 = Admin

  user2 = Procurement, Accounting

  user3 = Procurement

When I run the query above, it will show the user1 and user2.


texts: Selecting rows conflicting values in WHERE clause

texts:
I am having trouble selecting rows in MySQL. I do not want some rows to be selected, but they are being selected.

My query:

SELECT  name,url
    FROM  additions
    WHERE  enabled = 1
      AND  picture = 1
      AND  name LIKE '%value%'
      OR  tags LIKE '%value%'
      OR  subtext LIKE '%value%';

I do not want the rows that do not have a picture (picture = 0) and also rows that are not enabled (enabled = 0) to be selected. I think the rows match because either the name, tags and/or subtext matches the value, because of the OR condition in WHERE clause.

I want only the rows to be selected that have a picture and are enabled, regardless of the match in name, tags, subtext. How can I achieve that?


texts: Logical Sessions not closing at MongoDB 4.2

texts: How do I close my all idle connections in MongoDB as forcefully?


texts:
We are loading a huge dataset using SQLLDR and there are index creation that lasts for a couple hours or even more. Firewall drops this connection as there is no data transfer during index creation for an hour.
Oracle doesn't see this session as idle and doesn't drop connection, but firewall kills.

Do we have any workaround of this issue except changing firewall settings?


texts: Would appreciate some advice on using external data with SQL Server 2005

texts:
I'm a web developer with a bit of DB experience, but this one is beyond my knowledge.

We're trying to set up a new website. The data - products, prices, descriptions etc - are supplied by an external company in either .XLS, .CSV or XML format. This data is updates & distributed regularly, eg each month.

I could simply import all the data each time - the problem is we don't necessarily need all the products, so we would mark them as "unavailable" or something by creating a new field in the DB.

So, next time the data is provided, I need to import it again - but making sure it doesn't overwrite our "available" field.

My initial thought was to create 2 tables - one, "Products" which I would import the data into each time and a new table "availability" just with 2 fields - product ID and an available - yes/no field and join the tables on the product ID.

Is this a sensible approach? can XML files be read directly from SQL Server without importing?


texts: Database Schema for an address book with different mailing lists

texts:
I'm helping a friend out with taking an old excel, single-page database which contains around 12 different columns that store information about:

person first+last name
email(s)
current address
phone(s)
sorting code
receives mailing list
other information relating to their church and first cursillo attended

I started to normalize this into different tables.

person | address | email | phone | mailing | church | cursillo

I'm sure there's probably a few more tables I can get, the current database has a lot of missing data and some fields contain multiple email accounts or multiple phone numbers.

My friend doesn't have the time to learn about different databases or to learn programming so I want to make everything as simple as possible for him. For the new website, we're looking at setting it up in WordPress; the previous one was built by him in iWeb.

Ideally we want to be able to easily extract different mailing lists like:

Send e-mail to everyone who attended cursillo 50
  OR
Send e-mail to everyone who wants the monthly mail (mailing is just yes/no field option linked to person id)
  OR
Send e-mail to the new cursillo team for the upcoming weekend
  OR
Send e-mail to people who attend a specific church

etc.

However after splitting the tables up, I find that I'm not sure how easy it will be to maintain. What if my friend wants to change John Smiths' phone number, he'll have to go into person table and find the id for John Smith then go into the phone table to change the number.

What should I do to minimize the effort required to maintain such a database? as in, should I really normalize the database to 3NF/BCNF?


texts: How to restore Oracle 11g database without controlfile and spfile for a new server?

texts:
Hello everyone I have made a backup to my Oracle 11g dtabase with SID=ORCL

using the command backup database plus archivelog this my output after performing this command

Starting backup at 10-SEP-17 using target database control file instead of recovery catalog allocated channel: ORADISK_1 channel ORA_DISK_1: SID=97 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=C:\APP\MOD\ORADATA\ORCL\SYSTEM01.DBF input datafile file number=00002 name=C:\APP\MOD\ORADATA\ORCL\SYSAUX01.DBF input datafile file number=00003 name=C:\APP\MOD\ORADATA\ORCL\UNDOTBS01.DBF input datafile file number=00004 name=C:\APP\MOD\ORADATA\ORCL\USERS01.DBF channel ORA_DISK_1: starting piece 1 at 10-SEP-17 channel ORA_DISK_1: finished piece 1 at 10-SEP-17 piece handle=C:\APP\MOD\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2017_09_10\O1_MF_NNNDF_TAG20170910T180005_DVBNVP2J.BKP tag=TAG20170910T180005 comment=NONE channel ORADISK_1: backup set complete, elapsed time: 00:02:55 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 10-SEP-17 channel ORA_DISK_1: finished piece 1 at 10-SEP-17 piece handle=C:\APP\MOD\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2017_09_10\O1_MF_NCSNF_TAG20170910T180005_DVBO16L3.BKP tag=TAG20170910T180005 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 10-SEP-17
RMAN>

after I done this I copied  the whole Oracle root folder to external hard drive

I formatted the old server ...and I figure out that my configuration for controlfile autobackup is off which means the I only have database backup after I done this command backup database plus archivelog ...I'm searching many online tutorial but I don't know which one is suitable for me I'm really confused...I'm using windows.


texts: Get extract of Sybase data as INSERT statements, with order considering table dependencies

texts:
I want to create a SQL script to populate a DEV database using INSERT statements. (Preferably using INSERT statements, rather than binary form, so that I can safely check-in the script to version control).

I can easily extract the INSERT statements using various tools.  But I haven't found a way to extract the INSERT statements in an order so that when run, FK constraints will not be violated.

I've used SQL Server tools which accomplish this.  e.g. RedGate.  But cannot seem to find a Sybase compatible method.  I'd like a way to write the code to extract the T-SQL statements in the correct order to properly support relational integrity.


texts: I have an Oracle database and a user that has all privileges to access that database. Now I want to create a new read only user who can only get the DDL of that database and can only read the data. What are the least privileges or roles that are needed to be granted to the new user? I don't want to perform any kind of alter, update or delete on that database.


texts: Undocumented Wait Type QDS Loaddb and an unusable database

texts:
Has anyone ever encountered the wait type QDS_Loaddb? I have found zero documentation for this wait. The description on the microsoft site says TBD.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

I have an unusable database that shows QDS_Loaddb waits any time a query is run. It doesn't matter if the query is against system tables or user tables. The wait is the same QDS_Loaddb.

If I knew what this wait meant, I could have a shot at fixing it.


texts: Tell SQL Server a query is ad hoc and not to optimize based on it

texts:
Is there a way to run a one-off query against a production SQL Server database and tell it not to make any future judgments based on that query.  E.g. Store execution plans, recommend indexes, etc.

Or perhaps I shouldn't be so concerned.


texts: Is there a standard way to use common data and procedures across multiple databases?

texts:
We have several independent databases that have data and code in common, not in the sense that it is accessed between the databases but in the sense that the data means the same thing in each database, and the code does the same thing.

Examples are:

Configuration settings (e.g. error codes 50xxx, boilerplate text for company name, etc).
Procedures and functions that perform common tasks (e.g. converting a CSV string into a table, logging an error, formatting an error message based on the error code).
Table structures (e.g. table for database version history, table for logging errors). As well as the columns, constraints, and triggers there are also common procedures and functions that read/write the data.
Look-up tables (e.g. date look-up table containing dates between 2000-2100). These are similar to table structures but quite often the data will be the same across the databases. In the case of the date table, the start and end dates are configuration settings, which are read by a function, then the date data is generated by a procedure. All these operations are common between the databases.
User defined types (e.g. types for passing tables to functions).

For maintenance and support reasons I think it makes sense for things like error codes, procedures, functions, and types to have a "single point of truth" across all the databases, not a different truth in each database.

At the moment each database has it's own copy of everything, including source repository, and we maintain them all independently. This is far from ideal because it's too easy to fix a procedure in A and forget to put it in B, or add an error code to A and the same one to B but they mean different things, etc.

The databases are not updated at the same time and they don't necessarily reside on the same hardware. There are cases where they can read data from each other (if the other one exists).

Is there a standard way of having a single point of truth for data/code that is used across more than one database?


texts: Unable to update a record with a trigger using a dynamic column on a PostgreSQL DB

texts:
I'm trying to get a column named XXX_LASTCHANGE updated whenever a record is updated.

As the XXX would suggest, this is a dynamic value I want to pass with the trigger definition rather than creating dozens of similar triggers.

I came up with the following, but I cannot get this working as an error message is raised saying the error stack is full.

CREATE OR REPLACE FUNCTION update_lastchange()
RETURNS TRIGGER AS $$
BEGIN

    EXECUTE format('
        UPDATE "%s" t
        SET    "%s_LASTCHANGE" = NOW()
        WHERE  ($1)."%s_ID" = ($2)."%s_ID"',
        TG_TABLE_NAME, TG_ARGV[0], TG_ARGV[0], TG_ARGV[0])
    USING NEW, OLD;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER cnt_lastchange BEFORE UPDATE ON "CONTENT" FOR EACH ROW EXECUTE PROCEDURE update_lastchange('CNT');

I'm not sure what I'm missing. Any hint is really appreciated.


texts: Can a Z/OS mainframe application or DB2 read a SQL Server database/table directly?

texts: Is there anything similar to ODBC that would allow DB2 on a Z/OS mainframe platform to read a Microsoft SQL Server database/table.


texts: Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error

texts:
I recently upgraded from SQL Server 2005 to 2012. During validation however, a bug was discovered.

A certain trigger was coded as follows:

CREATE TRIGGER [dbo].[trigger] on [dbo].[foo]
FOR UPDATE, UPDATE
AS
    UPDATE foobar
    SET datetime = GetDate()
    FROM bar
    WHERE foobar.id = bar.id
GO

I can safely execute this (oddly) on SQL Server 2005.

However on SQL Server 2012 it throws (what I would expect) a syntax error.

  Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

Why does this not throw a syntax error on SQL Server 2005? My google-fu on this has failed me.

Why does this seemingly work on SQL Server 2005?


texts: Splitting Tables in MySQL. Good practice?

texts:
I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data.

The tables look like this:

[tableName_0]
[tableName_1]
[tableName_2]
[tableName_3]
[tableName_4]
[tableName_5]
[tableName_6]
[tableName_7]
[tableName_8]
[tableName_9]

The primary key is an integer id field. The application uses a hash algorithm (id mod 10) to know what table to access when doing lookups. For example id = 10 would result to [tableName_0].

Combined, the tables have probably 100,000 rows and the growth rate is relatively low.

So, my question is whether or not this is a viable solution or even if it's a good practice in any situation. My theory is to push to have them combined as it will make things easier as far as UNIONs, etc go. The main downside is changing all the application code and whether it is even worth it in the long run.


texts: PostgreSQL 9.3 - Move table to tablespace - Slow?

texts:
I need a help with moving table to a different tablespace.

I have a really large table that is not queried much often so I've decided to move it from tablespace on SSD to tablespace on HDD and save some space, but it is really slow (slower than it would be on HDD only).

Is there a general approach how to do it?
Can I keep the index files on the SSD tablespace?

I haven't found much about this topic anywhere so I guess it is not generally a good idea but if you could explain why, that would be perfect :)


texts: How to best encapsulate monetary values in a database

texts:
I am working on a multi-currency application. For the purpose of simplifying this question, we will not be considering currency conversion (hence I have deliberately left AsAt : datetime out of the equation). In a number of places I need to store monetary values (e.g. 1.23 USD). I understand that I need to store both the amount (1.23), and the currency (CurrencyID with FK to Currency table having ISOAlpha3Code = USD). My question is, how should I best encapsulate and isolate these two data from the other columns in the table (TableX) to which the monetary value relates?

As I see it, I have three options:

I store two columns in TableX, one being the Amount (numeric(p,s)) and one being the CurrencyID (FK). I add an XOR check constraint on the two columns to TableX.
(DBMS dependent) I create a CLR UDT (or equivalent) representing Currency, storing both Amount and CurrencyID. I store one Currency type column on TableX containing the monetary value.
I create a MonetaryAmount table with three columns: MonetaryAmountID (surrogate PK), CurrencyID (FK), and Amount. I add MonetaryAmountID (FK) to TableX.

I dislike #1 because I have used this pattern before and nobody has understood that the two data are part of the one complex datum; also, it's not normalized. I dislike #2 because it breaks 1NF, it is DBMS specific, and it will likely be difficult/expensive to enforce referential integrity on Currency. I'm rather ambivalent towards #3 - it feels over-engineered and will probably be computationally expensive, but it is correctly normalized.

#3 also has the added complexity of whether to place a unique constraint on the (CurrencyID, Amount) combination, thereby making it an ad-hoc reference table and better normalizing it. I believe it would be best not to do this, as whilst MonetaryAmounts may correlate, they are distinct in nature.

Any experience you can share (or alternatives you can suggest) would be appreciated. I would especially like to hear from genuine database architectural gurus.


texts: How to Configure SSRS Web Service Port via Command Line

texts:
It is straightforward to change the TCP port of SSRS using the GUI (Reporting Services Configuration Manager) but I am trying to fully script the installation and config of our SSRS servers.

Is it possible to change the TCP port of the SSRS web service via command line?

I have looked through this SSRS documentation from Microsoft, but don't see the option to change the TCP port.

UPDATE:
There is still no facility that I can find - as of SQL Server 2014 - to programmatically change the port of SSRS's web service. As per Microsoft, "You must write custom code that makes calls into the Report Server WMI provider. There are no command line utilities or script templates for configuring the URLs."

And even then, the port cannot be changed.

I tried editing the rsreportserver.config file and changing the port in there, but that resulted in "This page can’t be displayed" errors. Bah!


texts: Why is SQL Server 2008R2 using more memory than the "max server memory" setting?

texts:
Possible Duplicate:
  Microsoft SQL Server 2008 Memory

I've explicitly set the Maximum Memory value in SQL server 2008R2 to 13,000 MB, however when I check task manager I see it is using 14,071,448 KB (13,741 MB)

I'm concerned because the server crashed recently due to a low memory condition where SQL server had allocated almost all of the server memory.

I've checked and the number I have entered is correct (i.e not entered in KB by mistake).


texts: How to improve performance of a query matching on the same table

texts:
I have a requirement to find potentially matching customer records in the same table. The logic is as per below. However this seems to perform at O(N²). Is there anyway to improve the performance here? I've tried setting indexes, hashing the columns and comparing on that, etc but performance remains terrible over a large dataset. I've also added the query plan below.

SELECT
    C1.CustomerId AS Customer1,
    C2.CustomerId AS Customer2
FROM Customer C1
INNER JOIN Customer C2
    ON
    C1.CustomerId != C2.CustomerId
    AND
    (C1.FirstName = C2.FirstName OR C1.BirthDate = C2.BirthDate)
    AND
    (
        C1.EmailAddress = C2.EmailAddress
        OR
        C1.MobilePhoneNumber = C2.MobilePhoneNumber
        OR
        (
            C1.HomeAddressLine1 = C2.HomeAddressLine1
            AND
            (
                C1.HomePostCode = C2.HomePostCode
                OR
                C1.HomeSuburb = C2.HomeSuburb
            )
        )
    )


texts: What to do when I get “mysqld dead but subsys locked” in my amazon ec2 micro instance?

texts:
I have an Amazon ec2 micro instance that holds a WordPress site. Since yesterday, my mysql db keep crashing saying:

[ec2-user@ip-xxx-xx-xx-xxx ~]$ sudo service mysqld status
mysqld dead but subsys locked

I tried restarting the mysqld but after every 5 mins it keeps crashing with the same message - "Error establishing a database connection"

This is what my DB log file reads -

160123 05:44:41 mysqld_safe mysqld restarted
160123  5:44:43 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.46) starting as process 7928 ...
160123  5:44:44 [Note] Plugin 'FEDERATED' is disabled.
160123  5:44:46 InnoDB: The InnoDB memory heap is disabled
160123  5:44:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160123  5:44:46 InnoDB: Compressed tables use zlib 1.2.8
160123  5:44:46 InnoDB: Using Linux native AIO
160123  5:44:46 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160123  5:44:46 InnoDB: Completed initialization of buffer pool
160123  5:44:46 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160123  5:44:46 [ERROR] Plugin 'InnoDB' init function returned error.
160123  5:44:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160123  5:44:46 [ERROR] Unknown/unsupported storage engine: InnoDB
160123  5:44:46 [ERROR] Aborting
160123  5:44:46 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
160123 05:44:46 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

The common solution that found out there when I Googled for a solution for the issue - mysqld dead but subsys locked, is to create a swapfile. I did exactly as most of the solution suggest but since I'm not well versed with UNIX, I don't know what I have done is right or not.

I have a swap space of 1MB.

[ec2-user@ip-xx-xx-xx-xxx /]$ free
             total       used       free     shared    buffers     cached
Mem:       1019452     613820     405632         76      19124      64348
-/+ buffers/cache:     530348     489104
Swap:      1048572      46604    1001968

Or, Is there any other solution for this issue. Kindly help.


texts: why the date_format get prev day in MySQL 5.7

texts:
I am using this function to get the day from unix datetime:
select date_format(FROM_UNIXTIME(1606406400000/1000), '%Y-%m-%d')

to my supprise, I get the day of 2020-11-26.  but the day I get from time tool is 2020-11-07:

why would this happen and what should I do to fix it? It is a time zone problem, the MySQL timezone:
SELECT @@global.time_zone, @@session.time_zone;

SYSTEM  SYSTEM

MySQL is run in docker server, and the docker date is:
root@report-mysqlha-0:/# date
Mon Nov 30 10:48:23 UTC 2020

but I do not know how to make it right. My docker is running in kubernetes cluster.


texts: Why EXPLAIN doesn't show heap fetches for index scan

texts:
I tried to compare potential perfomance difference between covering b-tree index and simple b-tree index and was confused with EXPLAIN(ANALYZE,BUFFERS) output.

Test environment

-- function to fill test table
CREATE OR REPLACE FUNCTION fillTable (n INTEGER)
   RETURNS INTEGER AS $rowsCount$
DECLARE
   counter INTEGER := 0 ;
BEGIN

   IF (n < 1) THEN
      RETURN 0 ;
   END IF;

   LOOP
      EXIT WHEN counter = n ;
      counter := counter + 1 ;
      insert into key_value_test(key, value) VALUES (counter,counter);
   END LOOP ;

   return counter;

END ;
$rowsCount$
LANGUAGE plpgsql;

Test case for simple b-tree index

drop table key_value_test;
create table key_value_test
(
    key bigint not null,
    value integer not null,
    test VARCHAR(100),
    CONSTRAINT pk_key_value_test
    PRIMARY KEY(key)
);

SELECT  fillTable(1000000);

vacuum;

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

Test run results:

Index Scan using pk_key_value_test on key_value_test  (cost=0.42..8.44 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=1)
Index Cond: (key = 913680)
Buffers: shared hit=4
Planning Time: 0.062 ms
Execution Time: 0.026 ms

Test case for covering b-tree index

drop table if exists key_value_test;
create table key_value_test
(
    key bigint not null,
    value integer not null,
    CONSTRAINT pk_key_value_test
      PRIMARY KEY(key) include(value)
);
SELECT  fillTable(1000000);

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

vacuum;

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

Test run results

Before vacuum:

Index Only Scan using pk_key_value_test on key_value_test  (cost=0.43..8.45 rows=1 width=12) (actual time=0.127..0.128 rows=1 loops=1)
  Index Cond: (key = 740080)
  Heap Fetches: 1
  Buffers: shared hit=4
Planning Time: 0.078 ms
Execution Time: 0.146 ms

After vacuum:

Index Only Scan using pk_key_value_test on key_value_test  (cost=0.42..4.44 rows=1 width=12) (actual time=0.143..0.144 rows=1 loops=1)
  Index Cond: (key = 740080)
  Heap Fetches: 0
  Buffers: shared hit=4
Planning Time: 0.273 ms
Execution Time: 0.156 ms

My qiestion is why Heap Fetches shows only for index-only scans, but for index scans it information is hidden?
Now i see that i has same request count to shared buffers for simple index and covering index. So where is perfomance
impact for covering indexes in this situation? It realy strange, that i can't measure potential perfomance impact using EXPLAIN.


texts:
I want to create a new stored procedure but I got this error:

00955. 00000 -  "name is already used by an existing object"

The problem is I don't remember to have created an object with this name and, now, I would like to remove it (don't worry, it's not on my production database).

How can I find the type of this object in order to drop it correctly?


texts: ibdata1 grows exponentially when innodb_file_per_table is configured

texts:
I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently),  but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month).
Is My my.cnf file is correct?
Why my ibdata1 is so big (22GB)?
How I can look what there is in the ibdata1?
Server Configuration:

Debian 6 amd64

mysql-client-5.1  5.1.61-0+squeeze1

My InnoDB Configuration File:
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#

innodb_data_home_dir            = /var/lib/mysql
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_log_group_home_dir       = /var/lib/mysql
innodb_file_per_table
innodb_buffer_pool_size         = 5G
innodb_additional_mem_pool_size = 48M
innodb_log_files_in_group       = 3
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 0
innodb_lock_wait_timeout        = 50
innodb_thread_concurrency       = 15
innodb_flush_method             = O_DIRECT

Procedure:
1) Dump All DB's
2) Stop MySQL
3) Add "innodb_file_per_table"
4) Delete all ib* file
5) Start MySQL
6) Import All DB's

InnoDB Conf:
mysql> show variables like 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 50331648               |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 25165824000            |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    | /var/lib/mysql         |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | ON                     |
| innodb_flush_log_at_trx_commit          | 0                      |
| innodb_flush_method                     | O_DIRECT               |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 8388608                |
| innodb_log_file_size                    | 536870912              |
| innodb_log_files_in_group               | 3                      |
| innodb_log_group_home_dir               | /var/lib/mysql         |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_method                     | nulls_equal            |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 15                     |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+


texts:
I got a 18.3 GB .bak file, when restored, the database size is about 78 MB, including logs (54MB).

restore filelistonly from  DISK = N'E:\xx.bak'

BackupSizeInBytes
23068672
0

I understand that the opposite (backup smaller than restored database) might occur if there is unused space that was allocated in the source database, but this is not the case here.

Why the backup file size is much larger than the restored database?

The size on disk is 19,200,123 KB which is about 18 GB.


texts: How to correct this script restore and recover database on Oracle?

texts:
this script to restore and recover the target database when we : Removed the target database to simulate and setup a disaster recovery situation, the instance is shutdown, and all the configuration files are in their appropriate places, my script is :

SQLplus> shutdown immediate
RMAN> STARTUP MOUNT
RMAN > SHOW ALL
RMAN > RESTORE DATABASE CHECK READONLY
RMAN > RECOVER DATABASE DELETE ARCHIVELOG
RMAN > ALTER DATABASE OPEN RESETLOGS

at first we should create a read only tablespace called name_1
Shut down the database by using SQL plus tool :
Calling the recovery manager tool (RMAN) and make the database in mount mode.
Check the channels by using SHOW ALL statement
Applying the Restore statement with check read only
Applying the Recover statement
Start up the database in open mode


texts:
I'm creating the following table:

CREATE TABLE fund_identifier
(
    id BIGSERIAL PRIMARY KEY NOT NULL,
    identifier TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS fund_identifier_pkey ON fund_identifier (id);
CREATE UNIQUE INDEX IF NOT EXISTS fund_identifier_identifier_uindex ON fund_identifier (identifier)

However, I'm not sure what would be the best practice to use identifier as a foreign key. Should I use as reference the value of the foreign key like this:

    CREATE TABLE security_papers (
      id                                        BIGSERIAL,
      fund_identifier                           TEXT,
      as_of_date                                DATE,
          ...
      CONSTRAINT security_papers_fund_identifier_fk FOREIGN KEY (fund_identifier) REFERENCES fund_identifier (identifier)
    }

Or, should I use its id? Like this:

    CREATE TABLE security_papers (
      id                                        BIGSERIAL,
      fund_identifier_id                        BIGSERIAL,
      as_of_date                                DATE,
          ...
      CONSTRAINT security_papers_fund_identifier_fk FOREIGN KEY (fund_identifier) REFERENCES fund_identifier (identifier)
    }

My guess is on the second approach when it comes to the normalization rules. On the logical side, my application would need to look for  the id of fund_identifier first, before persisting anything at security_papers table. Right ?

But if I go with the second approach, this would be delegated to the database. Making my application logic, easier to implement.

Please let me know your thoughts, and if I'm missing some concept here. Thank you!


texts:
I'm getting a login error with the below sqlcmd to connect to a linked server:

SQLCMD -E -dlinkedserver -ig:\temp\script.sql -o g:\temp\test.txt

My linked server which is an oracle connects through the app account from the linked server.

Is there anything wrong with the above code, it tried to login to the linked server, run a scripts, and log to a file.


texts: SQL Anywhere Long Varchar/Binary Column Length Limited to 32767

texts:
Attempting to set up a database table (Sybase SQL Anywhere 11.0.1) to store user uploaded documents as either BLOBs or base64 encoded strings, but running into an issue where the length of my LONG BINARY and LONG VARCHAR columns is being limited to 32767.
Using the following create table statement:

CREATE TABLE blobTest (
blobCol LONG BINARY,
vcharCol LONG VARCHAR
)

The table is added to the database with no issue, however, running a query of the syscolumns table:

SELECT cname,coltype,length
FROM sys.syscolumns
WHERE tname LIKE 'blobTest'
ORDER BY cname ASC

Yields the result:

blobCol, long binary, 32767

vcharCol, long varchar, 32767

Storing a value in either of these columns shows that this is indeed the length of the columns, with anything larger than 32Kb* getting truncated.

Looking through the SAP documentation, it's my understanding that both of these data types should have a limit of roughly 2Gb. I've checked into the server/database configuration options but I can't find anything that looks like it would be limiting the length of these columns, am I missing something?

-edit-

The amount that the data was being truncated was not 32000, but 64000, per the admin datasource settings. Further testing showed that the results of:

SELECT LEN(vcharCol)
FROM blobTest

Would be the correct number of characters expected for an uploaded file.


texts: SSDT Publish failing with an error without even running any statements

texts:
My publish attempts are failing with this error

  Msg 205, Level 16, State 1, Line 7572 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Taking the old-school advice to print status messages between deployment script execution, I have narrowed it down to this:

The line number mismatches because of SQL Server quirks, which is why you have to PRINT stuff like a heathen (see the link above)

Note how there were literally no statements executed. I also had a verbose trace running which confirms that nothing was executed:

How could the control flow statements between two PRINT statements possibly cause an error?!


texts:
DBCC on the Cube fails(SQL version 2014 SP2, 12.0.5000.0).
My XMLA query:

Error message:
Executing the query ...
The DBCC element at line 7, column 88 (namespace https://schemas.microsoft.com/analysisservices/2016/engine) cannot appear under Envelope/Body/Execute/Command.
Run complete

I can find anything wrong with a query.Tried 2003 and 2014. According to books online, it should just work:
https://docs.microsoft.com/en-us/sql/analysis-services/instances/database-consistency-checker-dbcc-for-analysis-services?view=sql-server-2017


texts: How does Postgresql determine sort keys into the sort node?

texts:
This is the situation: I'm using Postgres 9.6 and had to migrate the database to a new server. This database is quite small, about 18MB in 25 tables but most of the records must be pulled in one query with 11 joins.
In the old server, everything was working well but after the migration and importing the database, this query takes 4 minutes.

Making explain analyze I found two important differences:

1) On the old database, Index Only Scan was triggered:

node type   count   sum of times    % of query
Hash    7   0.392 ms    0.0 %
Hash Left Join  6   68.972 ms   0.7 %
Hash Right Join 1   0.296 ms    0.0 %
Index Only Scan 1   0.342 ms    0.0 %
Index Scan  4   2.695 ms    0.0 %
Materialize 3   44.628 ms   0.4 %
Merge Left Join 4   24.674 ms   0.2 %
Merge Right Join    1   22.565 ms   0.2 %
Nested Loop Left Join   2   0.822 ms    0.0 %
Seq Scan    10  0.526 ms    0.0 %
Sort    4   10,133.864 ms   97.2 %
Unique  1   127.007 ms  1.2 %

Not in the new one:

node type   count   sum of times    % of query
Hash    10  254.372 ms  0.1 %
Hash Left Join  8   114.429 ms  0.0 %
Hash Right Join 2   1,907.899 ms    0.8 %
Index Scan  3   8.785 ms    0.0 %
Materialize 2   9.646 ms    0.0 %
Merge Left Join 3   42.783 ms   0.0 %
Nested Loop Left Join   1   1.322 ms    0.0 %
Seq Scan    12  2.404 ms    0.0 %
Sort    3   245,834.765 ms  97.3 %
Unique  1   4,462.125 ms    1.8 %

2) In the old database, only columns from the main table were Sort Keys in the Sort Node:

Sort Key: projects.id, projects.project_uid, projects.status, projects.name, projects.scale

But Postgres is taking every column of the select statements as Sort Key in the new server:

Sort Key: projects.id, projects.project_uid, projects.status, projects.name, projects.scale, organizations.id, organizations.name, countries.id...

and so on, about 50 sort keys and the Sort Node takes 98% of the time.

Autovacuum is enabled and I checked it for all the tables.

I don't know what is causing this, maybe Postgres is taking every column as a Sort Key because there is no Index Only Scan or the other way around, or maybe another reason. I would like to know your opinion about this, I'm not a DBA and I'm not finding a solution.

Thanks!


texts: NULL value causes blank row in SELECT results for text concatenation

texts:
I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results:

postgres=# SELECT '  (' || obj_id || ', ''' || obj_type || '''),' FROM il2.objects WHERE obj_id < 11 ORDER BY obj_id;
        ?column?
-------------------------
   (1, 'ScienceDomain'),

   (3, 'PIs'),
   (10, 'Instrument'),
(4 rows)

Doing a select *, it's pretty clear it's being caused by the obj_type being NULL for obj_id 2:

postgres=# SELECT * FROM il2.objects WHERE obj_id < 11;
 obj_id |   obj_type
--------+---------------
     10 | Instrument
      1 | ScienceDomain
      2 |
      3 | PIs
(4 rows)

(confirming it's NULL):

postgres=# SELECT * FROM il2.objects WHERE obj_type IS NULL;
 obj_id | obj_type
--------+----------
      2 |

Why is the result of the first SELECT giving me a blank row?
Even casting obj_type::text still gave me a blank row.

Additional Info:
The schema, for what it's worth:

postgres=# \d il2.objects
                                  Table "il2.objects"
  Column  |       Type        | Collation | Nullable |             Default
----------+-------------------+-----------+----------+----------------------------------
 obj_id   | integer           |           | not null | generated by default as identity
 obj_type | character varying |           |          |
Indexes:
    "objects_pkey" PRIMARY KEY, btree (obj_id)


texts: mysqld-multi with first DB as Slave and second DB as Master

texts:
Yesterday, I setup mysqld-multi in my.cnf. It works fantastic, but there is still a further Problem.

The first DB on socket 1 running as Slave from a other server. The second one from socket 2 should run as Master for a further Slave. The replication for DB 1 running successful.

My question is it possible to setup the master-slave environment for every socket?
Is it basically possible to setup more than one master-slave configuration in my.cnf?

I couldn't found helpful hints in google or I doesn't have the correct search words.

I'm happy for any helpful hints.

My system is Debian 6 with mysql 5 and php.

[mysqld1] read-only # comment out only on the productive
dbmaster port = 3306
datadir = /var/lib/mysql
pid-file = /var/lib/mysql/mysqld.pid
socket = /var/lib/mysql/mysql.sock
user = mysql
log-error=/var/log/mysql1.log
innodb_data_home_dir = /var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysqlinnolog
relay-log = /var/log/mysql/master-relay-bin
relay-log-index = /var/log/mysql/master-relay-bin.index
log-bin = /var/log/mysql/relaylog/master-bin

[mysqld2]
port = 3307
datadir = /var/lib/mysql-data2/mysqld2
pid-file = /var/lib/mysql-data2/mysqld2/mysql.pid
socket = /var/lib/mysql-data2/mysqld2/mysql.sock
user = mysql
log-error=/var/log/mysqld2.log
log-bin = /var/log/data2/mysql-bin

Both instance have a specific port and a separate Datadir. Is it possible to give each section in the snippet a unique ID? Can I put the Slave ID in section [mysqld1] and the master ID in [mysqld2]?


texts:
I have an asterisk PBX that has call recordings stored in a SQL database table.

This table has 9199 rows and is consuming 21GB on my server. I want to dump the data in this table to restore space on the server. I also only have 2GB of space left on my server. So could someone please help me with the following:

Is there an issue if I start to flush the table with only 2GB of space on my disk?
What would be the best command to use to flush this table?


texts: How to copy Azure SQL database to on-premises SQL Server daily for reporting?

texts:
I have a database as a service SQL instance in Azure, I want to copy this database to a reporting server located in a virtual machine, and this is not SQL as a service, so my COPY statement won't work, this is what I have:

I'm tryin to copy from dbserver-prod to the server marked in the red rectangle

This is the statement I've used to copy from SQL AAS instances and has been working fine but now I get an error: :

CREATE DATABASE Database_Reporting AS COPY OF [dbserver-prod].[database];

My aim is to have prod database copied to this server once a day for reporting. Any thoughts on how this can be done will be very helpful.


texts:
I have a transaction database that we recently went live with. The number of rows in the tables are starting to climb into the millions. So I am looking at how to purge the older data.

I have written purge processes before by just deleting the data I don't need anymore. But when you need to delete several million rows it can take a while and negatively impact performance and/or cause locks.

I have looked into table partitioning. The idea of partitioning the data and then just dropping a partition seems perfect.

However, from what I read, this cannot be done if the tables have foreign keys (my tables are mostly a chain of Master-Detail tables)

Is there a way to use partitioning with foreign keys, to delete lots of data?

Or some other cool way to delete large chunks of data without performance hits or locks?


texts: PostgreSQL: Cast any boolean field to integer in SELECT * query

texts:
In PostgreSQL, I can cast individual boolean fields to integer when I want to get 0/1 output instead of f/t.

SELECT some, thing, mybool::integer FROM mytable;

But is there a way to ask PostgreSQL to do it for any boolean field in the query, without specifying the field name?

If I have

SELECT * FROM mytable;

I would like any bools to be output as 0/1.


texts: Which file in "MySQL-5.0.27-win32" folder do i need to backup?

texts:
Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7).

Here is the current structure of of mysql-5.0.27-win32:

In "mysql-5.0.27-win32" folder:
bin
data
Docs
Embedded
examples
include
lib
mysql-test
scripts
share
sql-bench
my-huge.ini
my-innodb-heavy-4G.ini
my-large.ini
my-medium.ini
my-small.ini

In "data" folder:
mysql
mydatabase

...some other databases that I don't touch....

my-PC.err
my-PC.pid
ib_logfile0 ---> this is a file, not a folder
ib_logfile1 ---> this is a file, not a folder
ibdata1 ---> this is a file, not a folder

the "mydatabase" folder has file such as .frm, .MYD, .MYI for each of its table,
"mydatabase" also has "db.opt"

Ok, here is the scenarios. I finished developing my WebApp using mysql-5.0.27-win32in my Local Desktop PC. Now it is a time to deploy my app.

To deploy, I copy the whole mysql-5.0.27-win32 folder (which contains the current DB structure + some basic data) into Virtual Server.

So, there will be 2 same folders the original mysql-5.0.27-win32 in the Local PC & the copy  mysql-5.0.27-win32 in the Virtual Server. At the beginning, these 2 folders are exactly the same. They have the same DB structures (ie same number of tables & columns in the tables) & same data stored in the tables.

From now on, my website will store user data directly to the copy mysql-5.0.27-win32 folder in Virtual Server (VPS). But if the VPS got shutdown then I will lose all data.

Thus I want to back up daily the "mydatabase" from the copy mysql-5.0.27-win32.

So which file in the "MySQL-5.0.27-win32" folder do i need to backup so that when I put them into the original mysql-5.0.27-win32, then I can have exactly the same info as I have in the copy mysql-5.0.27-win32?


texts: What are the pitfalls of using "Preserve" for Cursor Behavior in Informix ODBC DSN?

texts:
I am converting an Informix SE to an IDS database.  The programs I have use ODBC System DSNs to communicate with the database.

For SE, we use the sesoctcp protocol.  The program code, would open a Recordset from the DSN, loop through each record and save each record to a local AccessDB (execute INSERT INTO), and then perform a MoveNext on the Recordset.

For IDS, we use the onsoctcp protocol.  With the same code, the program throws an error on MoveNext.

I believe the Recordset is being closed...  I added code to perform a MoveLast/MoveFirst on the Recordset before the loop and the MoveNext does not throw an error.  This change will require me to update potentially 400 programs.

On the other hand, I can forego the code changes and instead change the DSN Cursor Behavior to "1 - Preserve" and the MoveNext does not throw an error.  My limited understanding is that if I leave it on "0 - close", this tells the driver to decide when to close the recordset instead of letting the program decide.

The only documentation I could find for Cursor Behavior doesn't explain the values:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.odbc.doc/ids_odbc_069.htm

So... what are the pitfalls of using "Preserve" for Cursor Behavior in Informix ODBC DSN?


texts: How to store metadata derived from Named Entity Recognition (NER) in MongoDB documents?

texts:
I have collection of news articles in this format:

{
    "_id" : ObjectId
    "author" :
    "title" :
    "description" :
    "url" :
    "urlToImage" :
    "publishedAt" :
    "content" :
    "source" :
}

My plan is to run NER against the collection to tag the articles with various meta data such as companies mentioned, specified products, sentiment, geolocation, etc.

The long-term plan is to build a GraphQL API frontend and be able to search (Elasticsearch) or query the DB by the metadata.

My question is what’s the best format to store the metadata that would be best for querying a GraphQL API? My initial thought is store the metadata as a subdocuments like below, or maybe a entire different collection just for metadata:

{
    "_id" : ObjectId
    "author" :
    "title" :
    "description" :
    "url" :
    "urlToImage" :
    "publishedAt" :
    "content" :
    "source" :
    "metadata" : {
        "_id" :
        "organization" :
        "products" :
        "sentiment" :
        "location" :
        "topics" :
    }
}


texts:
I am new to database design and have been looking at some example data models here.

One thing I don't understand is if you have a table like client_addresses from the link above:

**************************
 client_addresses
**************************
 PF   client_id
 PF   address_id
 PK   date_address_from
      date_address_to
**************************

Im not quite sure I understand how the keys have been setup. It looks like there are three primary keys. Two are primary and foreign, another is just primary.

Is it the case that there are three primary keys? If so, why is this necessary?

Im using SQL InnoDB. How would I setup a table like this?


texts:
I have a table, and want to test if dynamic sql will compile.
Example:

create Table dbo.Product
(
     ProductId int,
     ProductName varchar(255)
)

This will succeed:  Column Name exists

declare @sqltext varchar(4000) = 'select ProductName from dbo.Product'
exec(@sqltext)

This will fail, Column Name  does not exist

declare @sqltext varchar(4000) = 'select ProductTestABCDName from dbo.Product'
exec(@sqltext)

How do I ensure dynamic sql will compile before running?

What I could do is create test stored procedure to compile, then drop the procedure afterwards, however seems inefficient. Is there a better way to compile code?

create procedure dbo.TestCompile as
drop procedure dbo.TestCompile

Is there like a sp_compilecode function in Sql server?


texts:
I'm creating a survey for visitors of my event.
However it's been a while since I created a database. So I need some help.

I found some solutions but they are way too extensive and that is something I don't need.

Visitors need to stay anonymous but they can leave their email behind (seperate table Emails that isn't linked to anything atm).
They have about 20 questions, some are open, some are one option(radio) and some are multiple options (checkboxes).
The questions need to be reusable.

That's about it. I just don't know how to go beyond the many-to-many in the diagram.


texts: If foreign keys/cascade deletes are bad, why use a database-server with that feature?

texts:
I noticed wordpress/rails etc do not use foreign keys constraint or cascade deletes features from database. Instead they handle this in PHP/Ruby/scripting level!

I have read this and this. Most arguments against foreign keys constraint talks about performance, multithreading, locking, scalability, etc.

Assuming arguments against foreign keys are valid, my question are:

If foreign keys are bad, why WordPress/Rails/etc uses a sql-server which supports foreign keys? Will they benefit going away from MySQL to a NoSQL kind of server?
On other hand, can applications coded in a way to make use of foreign keys feature without running into issues?
Is noSQL/redis better if we are using database only for storage and managing "relations" at application/script layer?


texts: MySQL Replication - Accidentally Dropped Database on Slave

texts:
I am using MySQL replication and I executed DROP DATABASE against my slave by accident (long story). Can I execute DROP DATABASE IF EXISTS against my master to safely drop the schema on my master without causing replication to blow up?

This schema is no longer being written to on the master.


texts: why Threads_connected falls down automatically in some cases

texts: How can I monitoring, when Threads_connected increase from which place(files) or when it decrease, because some of cases it dose not fall down automatically in our production server and we are getting to max connection error there, So i need to identify issues in which files has problem,  My site in magento 1.9 and max_connections=151 so i want to know what the ideal count for that and how can i monitoring ?


texts: Postgresql Streaming Replication - pgpool2 - failover

texts:
In my scenario I want pgpool to forward read only queries to slaves when the master goes down - I want my app to be in the "read only mode".

How do I setup pgpool to accept read only queries when master fails (streaming replication) ?

Currently I when the master goes down, pgpool waits for the master and doesn't forward any queries to slaves.


texts: Sql, get category tree for values from one table, names and ids

texts:
What I need
I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category).

Example
On web there is product with category tree.

In table "category" I have parent category id as 0, and other children categories ids are defined by numbers as this.

How can I in sql show all categories tree in one field? For example.

Úvod > Bydlení > Barvy a Laky > Vnitřní barvy

What is the simplest way to do that?

DBMS = PostgreSQL ver. 10.9.

Thank you very much.

BR.

Edited, updated, for ids main category id is not present.

By Akina solution.

with recursive

cte as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte.category_name, ' > ', s_category.category_name)
from s_category,cte
where cte.category_id = s_category.category_parent
),

cte2 as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte2.category_id, ' : ', s_category.category_id)
from s_category,cte2
where cte2.category_id = s_category.category_parent
)

select
cte.category_name as tree_category,
cte2.category_id as tree_id,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand

from
s_product

left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join cte on s_product.category_id = cte.category_id
left join cte2 on s_product.category_id = cte2.category_id

where
s_product.product_id = '133471'


texts: Failover strategy for SQL Server 2016 Standard Edition

texts:
I am using SQL Server 2016 Standard Edition.

I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02.
Users should be able to read from database in both server at any time and transactional replication allows that.

I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).

I know that transactional replication is not a HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.

Current failover strategy for a patch/maintenance in SQL01:

Transactional Replication on from SQL01 to SQL02.
So point the application to SQL02. Now new data comes into SQL02.
To keep SQL01 in synch with SQL02, restore full backup of the database from SQL02 and keep the SQL01 initialized.
Implement Log Shipping from SQL02 to SQL01.

For me this looks like a solution I can use. Possible issues I see are:

My database is like 2TB, so a backup of that in SQL02 will require 1 hour and restore in SQL01 will require 3 hours. Maybe I can avoid that by using a previous-night full backup for SQL02 and restore that at an earlier time. Please comment if it is okay to do that.
Other issue is, if I have regular transactional log backups of a SQL02 database (not from log shipping), would that also be restored by log shipping?

Do you see any issues other than these?

Please add your suggestion(s).